Combine two queries into one in Laravel Query Builder or SQL

Question

SQL queries are not one of my strong suits, and I have run into a problem that I was able to solve but am hoping to improve and make more efficient. I am using Laravel's Query Builder in these examples but don't mind using a raw request if I have to.

Currently I am making two queries as follows and merging them after.

 $acc_id = 10;
 $locat_id= 3;  

//First get all sites with account id
$allSites = collect(DB::table('sites')->where('acc_id', $acc_id)->get()):

//Next get only sites with account id and connections with current location id
$connectedSites = collect( 
    DB::table('sites')
        ->select('sites.id','name','active','url')
        ->join('connections as conn','sites.id','=','conn.site_id')
        ->where('acc_id',$acc_id)
        ->where('conn.locat_id',$locat_id)
        ->get()
);

//Merge the collections and drop first row with duplicate site_id
$sites = $allSites->merge($connectedSites)->keyBy('id');

return $sites;

So this gives me the desired results. E.g. all the sites that are associated with the account id, and also the connection data for sites associated with both the account and location ids. However, I would like to learn how to do this in one query if possible.

No some sites do not have a connection. They (the sites)are added by default when an account is created. Connections are made by the user at a later time. — skribe
– skribe, Commented Mar 16, 2018 at 20:56
Perhaps part of the issue could be poor design of how the table relationships work on my part. At this point it is working perfectly as I need it to, but I like to improve things as much as possible. — skribe
– skribe, Commented Mar 16, 2018 at 20:58

Jonas Staudenmeir · Accepted Answer · 2018-03-16 21:34:43Z

3

Try a LEFT JOIN:

$sites = DB::table('sites')
    ->select('sites.*','name','active','url')
    ->leftJoin('connections as conn', function($query) use($locat_id) {
        $query->on('sites.id', '=', 'conn.site_id')
            ->where('conn.locat_id', $locat_id);
    })
    ->where('acc_id',$acc_id)
    ->get();

edited Mar 16, 2018 at 21:34

answered Mar 16, 2018 at 20:16

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

skribe Over a year ago

A left join still does not return all the records with the $acc_id. Only those with both the $acc_id & $locat_id.

skribe Over a year ago

Actually this does work I think. But the $locat_id is out of scope or something and does not seem to get passed into the function. It works if I hard code it like ->where('conn.locate_id, 3)` but not if I do ->where('conn.locate_id, $locat_id)`

Jonas Staudenmeir Over a year ago

Sorry, I fixed my answer.

skribe Over a year ago

I missed the use statement the first time I looked at your fix. :) This is pretty awesome. I had no idea you could use functions in the query like this. This seems to produce the same results as what I was doing with my two query solution. Thanks a million!

skribe Over a year ago

Any idea where I can go to do some reading to improve my understanding of what is happening there?

|

N Mahurin · Accepted Answer · 2018-03-16 20:15:21Z

0

This can be done with one query, but I use eloquent instead of the query builder. Check out the with() method, it allows you to eager load the relationship.

$sites = Sites::with('connections')->where('acc_id', $acc_id)->get();

Now you can access the connections data from the model instance without needing to do anymore queries.

foreach($sites as $site){
    $site->connection->stuff; // No query generated
}

if you need to separate the two that's easy as well.

$connectedSites = $sites->where('connection.locat_id', $locat_id)
// No query generated from the above, but now $connectedSites has all of
// the connected sites

When you get into laravel's collections, you find out that a LOT of queries can be replaced by using them.

https://laravel.com/docs/5.6/collections#available-methods

answered Mar 16, 2018 at 20:15

N Mahurin

1,45611 silver badges18 bronze badges

2 Comments

skribe Over a year ago

Thanks for this. I use Eloquent a lot for single queries, and do really like collections and all the methods laravel provides for working with them. Your suggestions may indeed work, however my real goal is to learn how to construct the query in something more like SQL.

N Mahurin Over a year ago

Ah, I see. I’m much better with eloquent than I am with SQL sadly. Hope you find your answer.

Collectives™ on Stack Overflow

Combine two queries into one in Laravel Query Builder or SQL

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related