0

I posted this question yesterday but I think it's unclear so I deleted it and posted it again with more details. In my oracle database I have a USERS table with id_user defined as varchar and this varchar is like this: '145/1' ...... '145/9' so to add a new user I check the maximum value ('145/9') and add 1 to the second part of id_user (after the slash) so that the id_user is '145/10'.

The steps are like this:

  1. First: I'm using substr() to get the second part (after the slash) of all id_user.
  2. Second: I use Cast() to convert it to Int.
  3. Third: I use Max() to get the maximum value of Int numbers.

Finally in my laravel code I use the result of this query(the result is 9) and add 1 to it and insert a new user in the users table with id_user = '145/10' and so on. This query works fine but I need it in the Query Builder so I am trying a lot of queries but they didn't work.(please help me)

SELECT MAX(CAST(SUBSTR(id_user, INSTR (id_user, '/') + 1) AS INT)) AS Aggregate 
FROM "users" 
WHERE "ID_USER" LIKE '145/%';
Jon
  • 57
  • 3
  • 11
  • Search for `DB:raw()`, also I guess you should update your design to store these 2 values in 2 different attributes with correct data type so that you don't need that cast and substr type complicate stuff – M Khalid Junaid Jan 12 '21 at 19:11
  • I need cast and substr because max doesn't work correctly with mixed values (int and string) like this: '145/1' .... '145/6' .... '145/10' the max() function in this case gives me 145/6 instead of '145/10'. – Jon Jan 13 '21 at 09:35

1 Answers1

0

Finally, this query gives me the correct maximum:

 DB::table('users')->select(DB::raw('MAX(CAST(SUBSTR(id_user,INSTR(id_user, \'/\') + 1) AS INT)) as max')) ->where('id_user','like','145'.'/%')->get()[0];
Jon
  • 57
  • 3
  • 11