How to do this in Laravel subquery where in

Question

How can I make this query in Laravel   SELECT       p   id        p   name         p   img         p   safe name         p   sku         p   productstatusid   FROM  products  p  WHERE  p   id  IN       SELECT           product id       FROM  product category      WHERE  category id  IN   223    15     AND  p   active  1   I could also do this with a join  but I need this format for performance

User · Answer

You can use Eloquent in different queries and make things easier to understand and mantain:

$productCategory = ProductCategory::whereIn('category_id', ['223', '15'])
                   ->select('product_id'); //don't need ->get() or ->first()

and then we put all together:

Products::whereIn('id', $productCategory)
          ->where('active', 1)
          ->select('id', 'name', 'img', 'safe_name', 'sku', 'productstatusid')
          ->get();//runs all queries at once

This will generate the same query that you wrote in your question.

User · Answer

Product  from  products as p   - gt join  product category as pc   p id       pc product id   - gt select  p     - gt where  p active  1  - gt whereIn  pc category id     223    15    - gt get

User · Answer

Have a look at the advanced wheres documentation for Fluent  http   laravel com docs queries advanced-wheres  Here s an example of what you re trying to achieve   DB  table  users       - gt whereIn  id   function  query                 query- gt select DB  raw 1                 - gt from  orders                 - gt whereRaw  orders user id   users id               - gt get      This will produce   select   from users where id in       select 1 from orders where orders user id   users id

User · Answer

Consider this code   Products  whereIn  id   function  query        query- gt select  paper type id       - gt from with new ProductCategory - gt getTable        - gt whereIn  category id     223    15        - gt where  active   1     - gt get

User · Answer

You can use variable by using keyword  use   category id     category id   array  223   15    Products  whereIn  id   function  query  use   category id       query- gt select  paper type id        - gt from with new ProductCategory - gt getTable         - gt whereIn  category id    category id        - gt where  active   1     - gt get

User · Answer

The following code worked for me    result DB  table  tablename   - gt whereIn  columnName  function   query                     query- gt select  columnName2  - gt from  tableName2                   - gt Where  columnCondition       valueRequired                    - gt get

User · Answer

The script is tested in Laravel 5 x and 6 x  The static closure can improve performance in some cases   Product  select   id    name    img    safe name    sku    productstatusid                - gt whereIn  id   static function   query                     query- gt select   product id                        - gt from  new ProductCategory - gt getTable                        - gt whereIn  category id    15  223                               - gt where  active   1              - gt get      generates the SQL  SELECT  id    name    img    safe name    sku    productstatusid  FROM  products   WHERE  id  IN  SELECT  product id  FROM  product category  WHERE   category id  IN         AND  active

User · Answer

Laravel 4 2 and beyond  may use try relationship querying -  Products  whereHas  product category   function  query     query- gt whereIn  category id     223    15          public function product category     return  this- gt hasMany  product category    product id

User · Answer

Please try this online tool sql2builder   DB  table  products       - gt whereIn  products id  function  query                                DB  table  product category                               - gt whereIn  category id    223   15                                - gt select  product id                                   - gt where  products active  1      - gt select  products id   products name   products img   products safe name   products sku   products productstatusid       - gt get

User · Answer

using a variable   array IN Dev Table  where  id  1 - gt select  tabl2 id  - gt get     sel table2 Dev Table2  WhereIn  id   array IN - gt get

[php] How to do this in Laravel, subquery where in

Examples related to php

Examples related to mysql

Examples related to laravel

Examples related to eloquent

Examples related to subquery