how to efficiently convert a nested SQL in Query builder

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": [
        {}
    ],

score 0 · Answer 1 · answered Jul 05 '21 at 16:59

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 05 '21 at 16:59

IGP

14,160
4
26
43

Thanks for the explanation. But why I'm getting `Object of class Illuminate\Database\Query\Builder could not be converted to string` – astroboy1 Jul 05 '21 at 17:51
Where are you getting this error? – IGP Jul 05 '21 at 21:42
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. – astroboy1 Jul 06 '21 at 04:10
Maybe it depends on the laravel version. – shaedrich Jul 06 '21 at 07:05
@shaedrich so how do I resolve this? I have read few articles on mergeBindings, but I've done accordinly. Didnt work for me. – astroboy1 Jul 06 '21 at 07:09
Depends on what "didn't work" mean. Did you get any error message? – shaedrich Jul 06 '21 at 07:19
@IGP Do you know for chance since which version that's possible? ＠astroboy1 which laravel version do you use? – shaedrich Jul 06 '21 at 07:20

how to efficiently convert a nested SQL in Query builder

1 Answers1