Level 61
I would advise you to improve that SQL query before converting it. Don't know about you, but 500ms for a query is not acceptable.
Oct 30, 2021
21
Level 1
Query Builder with WhereIn ANDs
I'm wondering if anyone has any suggestions on how I might convert the following example SQL to Query Builder:
SELECT DISTINCT p.product_id
FROM products AS p
WHERE p.product_id IN
(
SELECT product_id
FROM product_components
WHERE product_id = p.product_id
AND code = 'RSTCW')
AND
(
SELECT product_id
FROM product_components
WHERE product_id = p.product_id
AND code = 'RSTLAD')
AND
(
SELECT product_id
FROM product_components
WHERE product_id = p.product_id
AND code = 'RSTRLAD')
AND
(
SELECT product_id
FROM product_components
WHERE product_id = p.product_id
AND code = 'RSTPSW')
My problem is that I've been unable to come up with a Query Builder version of this. The closest I've been able to get is to use chained $query->whereIn calls, but that results in unacceptably slow execution times if there are more than 3 or 4 subselects. Whereas, with the SQL above, total execution time never exceeds 500ms, regardless of the number of subselects.
So the question comes down to this: does anyone know how I might output this exact SQL using Query Builder? Thanks for any pointers you might have!
Level 1
Your advice is noted, thank you. For the purposes of this post, I'm only concerned with whether Query Builder is able to output this SQL.
Level 61
@sparkymain yes, it is capable, but your query is wrong. You are making things more complex than they have to be. But as you wish.
Level 1
@bugsysha In that case, perhaps I could ask you to help me out. First, if Query Builder is indeed capable of outputting that SQL, are you able to suggest how that's possible? I haven't been able to do it. Second, if you are willing to share a less complex way of writing that SQL (and of being able to use Query Builder to output it) I would be interested in seeing it. Thank you!
Level 61
@sparkymain let me first give you a hint, then you can come back with what you've tried and only then if you are unable to do it yourself I can give it a try.
Can your query be reduced to the following?
SELECT DISTINCT p.product_id
FROM products AS p
WHERE p.product_id IN (
SELECT product_id
FROM product_components
WHERE product_id = p.product_id
AND code IN ('RSTCW', 'RSTLAD', 'RSTRLAD', 'RSTPSW')
)
I think it can be.
1 like
Level 1
@bugsysha So that's interesting! Interesting for two reasons: First, because I think it does solve the problem as I posted it. I will try it and report back. Second, because I stripped down my example to such a degree that while your suggestion reduces the scope of the problem, I think I may still have problems using Query Builder still unless I find a way to address one more question with whereIn()... but one thing at a time! Thanks for this, and I'll come back when I've implemented your suggestions Thank you for sticking with me on this!
Level 1
@bugsysha OK, so that didn't work. The problem is that we are looking for product_ids of product_components which have code RSTCW AND RSTLAD AND RSTRLAD AND RSTPSW, so that needs to be accounted for by having separate SELECTs as in my original SQL, as far as I can tell. So if we assume, just for the sake of argument, that my original SQL is fine (which I concede may not be true) can you think of any way to make that happen with Query Builder?
Also, it turns out I was definitely mistaken about 500ms. With the DB facade in clocks at around 11ms, no matter the number of additional AND selects in WHERE IN.
Thanks
Level 61
@sparkymain since code is one column, it can't be at the same time all 4 values you've listed. So having one sub-select statement must work. Stripping down the example can sometimes cause problems because you might be hiding important details.
1 like
Level 1
@bugsysha Thanks for your thoughts. The reason your single subselect doesn't work in this case is that the last line of your subselect uses IN which ORs RSTCW, RSTLAD, RSTRLAD, RSTPSW... but I need them to be ANDed... as they are in my original SQL.
Level 61
@sparkymain I'm not gonna teach you about queries, but if you look at yours you will see that you are missing a parenthesis to wrap all those AND conditions for subselects and a way to concat those. Also, an additional piece of help is that you don't need subselects because you can do everything on the product_components table.
SELECT DISTINCT
product_id
FROM
product_components
WHERE code in('RSTCW', 'RSTLAD', 'RSTRLAD', 'RSTPSW');
I guess that is simple enough for you to know how to convert it to an Eloquent query?
$ids = \App\Models\ProductComponent::query()->select('product_id')->distinct()->whereIn('code', ['RSTCW', 'RSTLAD', 'RSTRLAD', 'RSTPSW'])->get();
Alternative so you don't get stuck with models:
$ids = \App\Models\ProductComponent::query()->select('product_id')->distinct()->whereIn('code', ['RSTCW', 'RSTLAD', 'RSTRLAD', 'RSTPSW'])->pluck('product_id');
And to check the query that was executed you can either listen to queries or do the following:
dd(\App\Models\ProductComponent::query()->select('product_id')->distinct()->whereIn('code', ['RSTCW', 'RSTLAD', 'RSTRLAD', 'RSTPSW'])->toSql());
That's all from me. Good luck.
Level 1
@bugsysha It looks like you did not see my final post before you posted this. I'm sorry you wasted your time replying. Perhaps I should have marked it as solved, which I'll do now if I can figure out how to do that.
Level 75
@sparkymain have you considered the db facade for that, a good example is:
https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
But just suggestion. Also you can use getPdo().
1 like
Level 1
Thank you. Yes, I'm actually currently using the DB facade and it's working, but for long term maintenance of what's becoming a pretty complex query I'd prefer to switch back to Query Builder.
The SQL above is just the one part of the query I have working with the DB facade... the one part that ultimately made me switch away from Query Builder because I couldn't figure out how to implement this particular WHERE IN part.
So basically, I'm hoping I can find a way to make Query Builder handle multiple subqueries in a single whereIn() call.
Level 75
@sparkymain it sometimes takes some trial and error. However, you are aware that no matter what you write in query builder, it converts to regular sql at runtime.
You can use the ->toSql() to see what query is actually run.
1 like
Level 1
@jlrdw Thanks! I definitely know about trial and error! I am indeed aware that Query Builder is just a convenience... but when you combine it's readability and the ease of binding, I'm hoping I can find a way to use it rather than raw(ish) SQL. I have used toSql() quite a bit, but since my development and calls are coming through the browser, I have been using Laravel Debugbar a lot and find it really convenient.
Level 1
I appreciate the thoughtful responses, and I think at this point I'll just see if anyone else chimes in while I continue to try to find a solution on my own. After days of Googling and trial and error and reading the docs and asking the question here and coming up empty-handed, I suspect it may just not be possible with query builder. But it seems like such an obvious thing that people would want to do that I'm surprised by that.
For anyone else reading this, please assume that the original SQL I posted is EXACTLY what I'm hoping to duplicate in query builder. Even if you don't love the SQL or think it can be improved please understand that that is beyond the scope of my question. The SQL is valid SQL, it works, and I'm just wondering how it might be created using Query Builder. Thanks!
Level 51
@sparkymain Try that.
DB::table('products AS p')->distinct('p.product_id')
->whereIn('p.product_id', function($query){
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTCW');
})
->whereIn('p.product_id', function($query){
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTLAD');
})
->whereIn('p.product_id', function($query){
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTRLAD');
})
->whereIn('p.product_id', function($query){
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTPSW');
})
However, you question mentioned that query is slow, if my answer works you will get the same result but will not do any good for the execution time. To solve the query execution time try to find better query without that number of nested queries or change your DB scheme to be perform.
1 like
Level 1
@MohamedTammam Thanks for such a fast response! So that's the closest I got as well. Unfortunately it outputs different, much slower, SQL than the SQL I'm trying to convert to query builder. Your suggestion outputs a new AND WHERE IN for each code, whereas the SQL in my post has only a single WHERE IN. I really appreciate your suggestion though... and it really does make me think that query builder maybe is just not able to output that SQL.
Level 1
After @mohamedtammam replied, I thought I'd put some pseudo code which absolutely does not work, but is at least a way for me to think about possible solutions and might help others visualize the problem I'm trying to solve:
DB::table('products AS p')->distinct('p.product_id')
->whereIn('p.product_id', function($query){
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTCW');
AND
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTLAD');
AND
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTRLAD');
AND
$query->select('product_id')
->from('product_components')
->where('product_id', '=', DB::raw('p.product_id'))
->where('code', '=', 'RSTPSW');
})
Level 1
As @bugsysha suspected, this was a case of a me misunderstanding my SQL followed by poor simplification by me of that SQL. The problem and solution I found are so unrelated to the question I posted that I'm not going to bother to post anything here. I will say that whereRaw() came in very handy. I appreciate the thoughtful responses from people here. Next time I have a question I will try to have it better in hand before I post!
Level 51
@sparkymain If that solved your questions, please mark it as solved.
Please or to participate in this conversation.