Question 299042 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to use multiple subqueries in Laravel's Eloquent?

I have a SQL query like this:

SELECT year
FROM (
    SELECT my_custom_function_for_get_year(week_start::date) AS year
    FROM (
        SELECT date_trunc('week', week_start) AS week_start
        FROM generate_series('2023-01-01'::date, '2023-12-31'::date, interval '1 week') gs(week_start)
    ) as select1,

    (SELECT id FROM mytable) as select2

) as gs_series
ORDER BY gs_series.year;

I’m trying to achieve the same using Eloquent. I thought there might be something like an addFrom method, but it’s not there, and it simply sets the last from, replacing the first one.

MyModelClass::select([DB::raw("year")])->from(function(Builder $generateSeries) {
    $generateSeries->select([
        DB::raw("my_custom_function_for_get_year(week_start::date) AS year")
    ])
        ->from(function($query) {
            $query->select([
                DB::raw("date_trunc('week', week_start) as week_start")
            ])
                ->from(
                    DB::raw("generate_series('2023-01-01'::date, '2023-12-31'::date, interval '1 week') gs(week_start)")
            );
        }, 'select1')
        ->from(function($query) {
            $query->select(['id'])->from('mytable');
        }, 'select2');
    
    }, 'gs_series');

As a result, it looks like this:

    select 
      year 
    from 
      (
        select 
          my_custom_function_for_get_year(week_start :: date) AS year 
        from 
          (
            select 
              "id" 
            from 
              "mytable"
          ) as "select2"
      ) as "gs_series" 
    order by 
      gs_series.year

I assume I can do this using DB::raw(), but I would like to add when conditions later, and with DB::raw(), it won’t be very convenient.

Tags:

2

Answers


  1. 0

    To achieve a similar result using Eloquent in Laravel, you can use the selectRaw method to include raw SQL expressions in your select statement. Here’s an example:

       use IlluminateDatabaseQueryBuilder;

$years = MyModelClass::selectRaw('my_custom_function_for_get_year(week_start::date) AS year')
    ->from(function (Builder $generateSeries) {
        $generateSeries->selectRaw('date_trunc('week', week_start) as week_start')
            ->fromRaw("generate_series('2023-01-01'::date, '2023-12-31'::date, interval '1 week') gs(week_start)");

        $generateSeries->join('mytable', function ($join) {
            $join->on('some_condition', '=', 'some_other_condition'); // Adjust the conditions accordingly
        });
    }, 'gs_series')
    ->orderBy('year')
    ->get();

dd($years);

    In this example, I’ve used selectRaw to include the raw SQL expression for the year column. The from method is used to create a subquery. I’ve also used join to join the mytable and generate_series subqueries. You should adjust the join conditions based on your actual requirements.

    Login or Signup to reply.
  2. 0

    The Builder::fromSub() method is not mentioned in documentation, but appears to be what you’re looking for. It accepts a closure, but I’m passing the subqueries as variables to keep things more readable.

    I’m also using the Builder::selectRaw() method; you should be able to replace values with ? in the query and pass them as parameters instead. Good practice in modern versions of Laravel is to avoid DB::raw() whenever possible.

    $select1 = function(Builder $q) {
    $q->selectRaw("date_trunc('week', week_start) AS week_start")
        ->fromRaw("generate_series('2023-01-01'::date, '2023-12-31'::date, interval '1 week') gs(week_start)");
};

$select2 = function(Builder $q) {
    $q->select("id")->from("mytable");
};

$gs_series = function(Builder $q) {
    $q->selectRaw("my_custom_function_for_get_year(week_start::date) AS year")
        ->fromSub($select1, "select1")
        ->joinSub($select2, "select2");
};

$results = MyModelClass::select("year")->fromSub($gs_series, "gs_series");

    Obviously I have no way to test this, and some of the SQL syntax is unfamiliar to me (I use MySQL) but hopefully it gets you at least most of the way to a solution. You may also want to investigate if some of this, like the date manipulation, can be done on the PHP side to simplify the query some more.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search