CODEWITHSUNDEEP
phpsqllaravellaravel-5
skribe
skribe

Reputation: 3615

Combine two queries into one in Laravel Query Builder or SQL

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.

Upvotes: 2

Views: 3575

Answers (2)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

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();

Upvotes: 3

N Mahurin
N Mahurin

Reputation: 1446

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

Upvotes: 0

Related Questions