haheute haheute - 5 months ago 10
SQL Question

How to use one query for different selects in laravel?

I thought it would be a good idea to define a query and use it for several selects or counts later, but it does not work. The second select has both

where
s in the sql statement:

$query = Pic::where('pics.user_id',$user->id);

if($cat) $query->where('cat',$cat);
if($year) $query->where('jahrprod',$year);

$zb = $query->select('pics.id','pics.title','pics.created_at')
->where('pics.id', '>', $pic->id)
->orderBy('pics.id')
->take(2)
->get()->reverse();

$za = $query->select('pics.id','pics.title','pics.created_at')
->where('pics.id', '<', $pic->id)
->orderBy('pics.id')
->take(13)
->get();


Query:

SELECT `pics`.`id`, `pics`.`title`, `pics`.`created_at`
FROM `pics`
WHERE `pics`.`user_id` = '3'
AND `pics`.`id` > '2180'
AND `pics`.`id` < '2180'
ORDER BY `pics`.`id` ASC, `pics`.`id` ASC
LIMIT 13


I tried to "pass it as reference" i.e.
&$query->select...
but "only variables can be passed as reference".

How can I use the query , or save it, and use it for both actions. Is it possible?

Answer

You are updating object state with the statements when you do $query->where(), so yeah, when you're doing a second select, all conditions from the first one are still there. Thats the reason why these lines work without any assignments:

if($cat) $query->where('cat',$cat);
if($year) $query->where('jahrprod',$year);

To achieve described behaviour you would need to create an query object copy:

$query = Pic::where('pics.user_id',$user->id);

if($cat) $query->where('cat',$cat);
if($year) $query->where('jahrprod',$year);

$queryClone = clone $query;

$zb = $query->select('pics.id','pics.title','pics.created_at')
            ->where('pics.id', '>', $pic->id)
            ->orderBy('pics.id')
            ->take(2)
            ->get()->reverse();

$za = $queryClone->select('pics.id','pics.title','pics.created_at')
            ->where('pics.id', '<', $pic->id)
            ->orderBy('pics.id')
            ->take(13)
            ->get();

Notice that mere assignment would not work here:

$queryClone = $query;

Because that would pass object reference and would result in the same behaviour as in your case. Clone creates a full object copy.

http://php.net/manual/en/language.oop5.cloning.php