Use field returned from find to add more results in same query

Question

Use field returned from find to add more results in same query

Navigation

#1 by (1 votes)

1

In the code below I look for a product and get its "related" field to search the bank for all products (including itself) that have the same value in that field.

Is it possible to do all this with just one query instead of two?

$produto = Produto::find(7996);

$relacionados = Produto::where('relacionado', $produto->relacionado)->get();

php laravel laravel-5.2

asked by anonymous 03.03.2018 / 03:10

1 answer

Calculation using filled fields jQuery How to transform a UNIX timestamp into DateTime?

score 1 · Accepted Answer

You can use whereRaw to write Sub Query as follows:

$subQuery = 'relacionado = (SELECT relacionado FROM produto WHERE id=? limit 1)';
Produto::whereRaw($subQuery, array(7996))->get();

would then be a SQL but having a SubQuery for the search of the relacionado field.

The way you did it is not wrong it's just wrong to bring all the fields that are not required for the second search, an optimization:

$result = Produto::where('id', 7996)->select('relacionado')->first(); // otimizado
$relacionados = Produto::where('relacionado', $result->relacionado)->get();

A summary of the code can be done as explained in this question with Query Scope as follows:

Create a method in your class Produto :

public function scopeRelacionado($query, $id)
{
    $subQuery = 'relacionado = (SELECT relacionado FROM produto WHERE id=? limit 1)';
    return $query::whereRaw($subQuery, array($id))->get();
}

and use as follows:

Produto::relacionado(7996);