how to efficiently convert a nested SQL in Query builder [duplicate]

Question

I've a nested sql query

SELECT
   city_id,
   town_id,
   SUM(IF(total_visit >= 2, 1, 0)) visited_twice,
   SUM(IF(total_visit >= 3, 1, 0)) visited_thrice 
FROM
   (
      SELECT
         c.city_id,
         c.town_id,
         c.id,
         COUNT(v.id) AS total_visit 
      FROM
         VISITS v 
         LEFT JOIN
            CUSTOMERS c 
            ON v.customer_id = c.id 
      WHERE
         c.customer_type = 1 
         AND MONTH(v.visit_date) = 6 
         AND YEAR(v.visit_date) = 2021 
      GROUP BY
         c.town_id,
         c.id,
         MONTH(v.visit_date),
         YEAR(v.visit_date) 
      HAVING
         total_visit > 1
   )
GROUP BY
   town_id

How can I convert into Query builder pattern to make the code bit readable?

I've tried converting to query builder for normal queries, but looking for advices for a nested query.

EDIT

$visitTable = Visit::$TABLE_NAME;
$customerTable = Customer::$TABLE_NAME;

$sub = Visit::with($with)
    ->selectRaw("$customerTable.city_id, $customerTable.town_id, 
                     $customerTable.id, COUNT($visitTable.id) total_visit")
    ->leftJoin("$customerTable", "$customerTable.id", '=', "$visitTable.customer_id")
    ->where("$customerTable.customer_type_id", 1)
    ->whereMonth("$visitTable.visit_date", $month)
    ->whereYear("$visitTable.visit_date", $year)
    ->groupBy("$customerTable.town_id, MONTH($visitTable.visit_date), YEAR($visitTable.visit_date)")
    ->havingRaw('total_visit > 1');

$query = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->selectRaw("city_id, town_id,
                SUM(IF(total_visit >= 2, 1, 0)) visited_twice, SUM(IF(total_visit >= 3, 1, 0)) visited_thrice ")
    ->mergeBindings($sub->getQuery())
    ->groupBy("town_id");

But ending up with this

"connection": {},
    "grammar": {},
    "processor": {},
    "bindings": {
        "select": [],
        "join": [],
        "where": [
            1,
            "6",
            "2021"
        ],
        "having": [],
        "order": [],
        "union": []
    },
    "aggregate": null,
    "columns": [
        {}
    ],

IGP · Accepted Answer · 2021-07-05 16:59:16Z

0

You can easily query from subquery tables by using any of the following syntax options

DB::table(Closure, alias)
DB::table(Builder, alias)
DB::query()->from(Closure, alias)
DB::query()->from(Builder, alias)

Here, I'm using the second option.

// Build the subquery first (without getting the results)
$visits_sub = DB::table('VISITS', 'v')
    ->select('c.city_id', 'c.town_id', 'c.id')
    ->selectRaw('count(v.id) as total_visit')
    ->leftJoin('CUSTOMERS as c', 'v.customer_id', 'c.id')
    ->where('c.customer_type', 1)
    ->whereMonth('v.visit_date', 6)
    ->whereYear('v.visit_date', 2021)
    ->groupByRaw('c.town_id, c.id, month(v.visit_date), year(v.visit_date)')
    ->having('total_visit', '>', 1);

// Use the built subquery as the table
$query = DB::table($visits_sub, 'visits')
    ->select('city_id', 'town_id')
    ->selectRaw('sum(if(total_visits >= 2, 1, 0) as visited_twice')
    ->selectRaw('sum(if(total_visits >= 3, 1, 0) as visited_thrice')
    ->groupBy('town_id');

// you can verify the sql by dumping the query
$query->dump();

$results = $query->get();

If you want to force the results to be Visit models, you need to go with the ->query()->from(...) syntax.

$query = Visit::query()
    ->from($visits_sub, 'visits')
    // rest should be the same.

answered Jul 5, 2021 at 16:59

IGP

16.4k5 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

astroboy1 Over a year ago

Thanks for the explanation. But why I'm getting Object of class Illuminate\Database\Query\Builder could not be converted to string

IGP Over a year ago

Where are you getting this error?

astroboy1 Over a year ago

Basically, I'm using this query builders in my repository pattern and at the repository level. When I'm testing this apis on postman, I'm getting this.

shaedrich Over a year ago

Maybe it depends on the laravel version.

astroboy1 Over a year ago

@shaedrich so how do I resolve this? I have read few articles on mergeBindings, but I've done accordinly. Didnt work for me.

|

Collectives™ on Stack Overflow

how to efficiently convert a nested SQL in Query builder [duplicate]

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related