Level 63
That's a good question.
You can perhaps try to submit your query as a raw query, but it doesn't really answer your question.
1 like
Jun 7, 2022
10
Level 1
translate raw query for eloquent
I'm having some trouble with translating the following SQL query for better reading in eloquent:
SELECT @number tested_number, 7 - LENGTH(nums.num) common_digits, tickets.*
FROM tickets
JOIN (SELECT 1 num UNION
SELECT 10 UNION
SELECT 100 UNION
SELECT 1000 UNION
SELECT 10000 UNION
SELECT 100000) nums
WHERE @number DIV nums.num = tickets.ticketNumber DIV nums.num
ORDER BY nums.num LIMIT 1;
would be create if someone could help me out 🙏
Level 1
as explaination:
I've a table with the attribute "ticketNumber" which is a string (6) like 123456 the query selects all tickets starting with the same chars (= @number)
e.g.
- 133333 is in common_digits 1
- 122222 is in common_digits 2
- 123222 is in common_digits 3
- ...
- 123455 is in common_digits 5
- 023456 has no common_digits
I wanted to implement this logic with this query. It has only two problems:
- e.g. 123455 is in common_digits 1,2,3,4 and 5 instead of only common_digits 5
- not able to use it in Laravel :D (even with raw query @vincent15000 )
maybe you have a better idea to solve this problem?
Level 73
@adrian.cmd Can you supply some more examples and the desired result from those samples?
3 likes
Level 102
@adrian.cmd why is 133333 in common_digits 1? Shouldn't the common digit be 3?
2 likes
Level 1
I have a table with 6 digit numbers (= ticketNumber) that can range from 0-9 and I would match that against a number in 6 categories
- first number match
- first two number match
- first three number match
- first four number match
- first five number match
- all numbers match
But only the highest category should be selected. An example
Number: 123456
If one has the number [123]756 then this would fall into category first three, number match On number 023456 then this would be no match
hope this helps this for a better understanding of the issue?
1 like
Level 75
You can use the db facade.
2 likes
Level 1
@jlrdw could you help how to use the DB facade with this query?
Level 73
@adrian.cmd If it's valid SQL the just wrap it in a DB::select('<your query here>')
2 likes
Level 75
@adrian.cmd also see example in the documentation, do not forget to bind any parameters.
https://laravel.com/docs/9.x/database#running-a-select-query
2 likes
Level 1
@jlrdw thanks
it's running with that - sadly it's not nice to read.. but it works - most important :D
Please or to participate in this conversation.