SQL with data associated with Laravel?

Asked

Viewed 48 times

2

I have the following tables listed below

Products

  • id
  • product
  • value
  • validity

Consultation

  • id
  • user_id

Consults_products

  • id
  • query
  • product

Whenever a user performs a query, the history is saved in the table consultas and the products referred to in the table consultas_produtos through the relationship.

How can I build a SQL that when listing the products according to the query parameters, return only the products that were never consulted by the user?

laravel-5 laravel-eloquent

  • You got the appointment you’re doing???

  • 1

    $products = Product::Where('value', '>=', $value)->get(); This will return all products that have the value greater than specified. I need to return only products that have never been queried by the user by checking the history through the association.

  • What is the? version of your Laravel? This information is important.

  • 1

    My version of Laravel is 5.6

1 answer

2


The method may be used Join and at the end a Where to filter users, example:

$p = Produto::join('consultas_produtos','produtos.id','=','consultas_produtos.produto_id') 
               ->join('consultas','consultas_produtos.consulta_id','=','consultas.id') 
               ->where('consultas.user_id','<>', $user_id)
               ->get();

References:

  • In this way it would not work, because users who have never consulted a product (pineapple, for example) could not query it, because some user has already consulted it.

  • @Maxpercent your question says Product? or am I wrong? and what didn’t work? The question is this: Como posso construir uma SQL que ao listar os produtos ... Surely there is missing data in your question

  • @Maxpercent other thing by your comment, there is a great confusion who query in which I have consulted, I am not understanding.

  • The method 'doesnHave' would not return in SQL the products that have already been consulted (because they already have a record in the table 'consultas_products'). However, let’s say that the user A sees the pineapple product, because it is within the price range stipulated by him. The consultation will be performed and the relationship saved. Using the proposed method, when it performs a new query using the same price range, the pineapple product will not be returning. But this way if a user B does the same user query he wouldn’t return the pineapple product, right?

  • @Maxpercentage then you need to make an INNER JOIN of the tables and at the end make a filter through the field user_id <> the value, really research becomes restrictive to the relationship and will not satisfy what needs.

  • When a user performs a query it is recorded in the 'query' table the user id and the parameters he used. In the table 'queris_products' is recorded the id of the table 'query' next to the id of each product consulted by the user. I need to check which products that user has already consulted not to return in futile queries. The method you propose works perfectly for this, however, if user_2 queries the same parameters already used by user_1, it will not return products that have not yet been searched by user_2.

  • I made the change @Max%

  • @Maxpercentageo worked out the solution?

Show 3 more comments

Browser other questions tagged laravel-5 laravel-eloquent

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