Laravel - Select in multiple tables

Asked

Viewed 580 times

1

My goal in the code is:
- Create a filter option for the customer, where he will have the options to filter the product by: COLOR, GROUP, FEATURE AND MEASURES.

My problem is:
- Create the query that accepts array’s and accepts when one of the filter options is not selected. For example: I only want to filter by color, but not by other options.

Code I have so far.

$produtos = DB::table('products')                  
              ->join('cores_produto','cores_produto.produto_id','products.id')
              ->join('grupos_produto','grupos_produto.produto_id','products.id')
              ->whereIn('cores_produto.cor_id', $c )
              ->whereIn('grupos_produto.grupo_id', $g)                  
              ->groupBy('products.id')->orderBy('products.id')
              ->select('*')
              ->toSql();

Return of sql query by selecting only the color option.

select * from `products` inner join `cores_produto` on `cores_produto`.`produto_id` = `products`.`id` inner join `grupos_produto` on `grupos_produto`.`produto_id` = `products`.`id` where `cores_produto`.`cor_id` in (?) and 0 = 1 group by `products`.`id` order by `products`.`id` asc
sql laravel query

  • Boy I couldn’t understand your question, it has to explain otherwise or more clearly ?

  • For example, I go in the filter and I want to know all the products that have the color "Blue and Yellow", but I do not select anything in the group, it returns me an empty query, because instead of it does not play in the query what is null it plays the value 'and 0 = 1', here it comes empty.

  • where would enter this null value ? I’m still not sure which is your problem.

  • Your question is not very clear

  • @Virgilionovic sorry I’m not being very clear staff. I edited the post for a better understanding.

2 answers

3


Good afternoon adrib,

I don’t know much about php, but with a quick search on the web I found an interesting alternative to solve your problem.

If I understand correctly, the problem can be summarized in: "I want to generate a query from the filter that the user will fill, and ignore the fields that he did not fill", is correct?

If yes, you can try to perform the query via Eloquent ORM from a method when(teste, function()), performing a function if the test parameter is TRUE,

Example:

$produtos = Produto::when(Request::input('produto'),function($query){
                        $query->where('nome_produto',Request::input('produto'));
                    })
                    ->when(Request::input('preco'),function($query){
                        $query->where('preco_produto',Request::input('preco'));
                    })
                    ->when(Request::input('categoria'), function($query){
                        $query->whereHas('categoria', function ($query) {
                            $query->where('nome_categoria',Request::input('categoria'));
                        });
                    })->get();

Here we have a code that will check if the filter field has been filled in, if yes, will run the function($query), and after you have checked all the fields will give a get() and generate your query query.

I hope I helped, but if you have not solved yet the problem gives a search in the references, maybe you can understand better from them.

Hugs.

- References:

Filter Query via Eloquest ORM

Requests & Input - Laravel

  • Good afternoon, my friend. EXACT: "I want to generate a query from the filter the user will fill in, and ignore the fields he did not fill in" Sometimes we lack words. I will take this test now and after sharing the results. .

1

Solution

To be able to help anyone with the same problem as me, I marked our friend @H.Lima’s answer above as the solution that made me think and solve the problem.
Below I will describe my code and what I needed to understand.

$produtos = Produto::when(Request::input('cores'), function($query){
                  $query->whereHas('relacionamentoCores', function ($query) {
                      $query->where('cores_produto.produto_id',Request::input('cores'));
                  });})->get();

I was not understanding how I would make the relationship of the tables within the explanation cited above by the friend, It was then I realized after the whereHas should be the relationshipCores that was part of the relationship between Models. So the answer to the question

I want to generate a query from the filter the user will fill in, and ignore the fields he did not fill in.

fits perfectly in the above answer.

Browser other questions tagged sql laravel query

You are not signed in. Login or sign up in order to post.