Merge into one query two distincts

user_16711532 0
Viewed 37

How do I need to make this into one query?

 $yearsInvoices = ModelOne::query()
      ->select(DB::raw('DISTINCT(YEAR(date)) as year'))
      ->groupBy('year')
      ->pluck('year')
      ->toArray();

  $yearsExpenses = ModelTwo::query()
       ->select(DB::raw('DISTINCT(YEAR(date)) as year'))
       ->groupBy('year')
       ->pluck('year')
       ->toArray();

  $years = \array_merge($yearsExpenses, $yearsInvoices);

Would appreciate some help. It's possible to join those two tables like this:

->join(modeltwo_table, modeltwo_table.c_id, modelone_table.c_id)

What I had:

 $yearsInvoices = ModelOne::query()
      ->join(modeltwo_table, modeltwo_table.c_id,'=', c_id)
      ->select([
          DB::raw('DISTINCT(YEAR(date)) as modelone_year'), 
          DB::raw('DISTINCT(YEAR(modeltwo_table.date)) as modeltwo_year')
      ])
      ->groupBy('modelone_year')
      ->groupBy('modeltwo_year')
      ->pluck('modeltwo_year', 'modelone_year')
      ->toArray();

But it didn't work.

laravel
2 Answers

What could work for you would be some sort of a union:

  $yearsInvoices = ModelOne::query()
      ->select(DB::raw('DISTINCT(YEAR(date)) as year'));

  $yearsExpenses = ModelTwo::query()
       ->select(DB::raw('DISTINCT(YEAR(date)) as year'));
  
  $allYears = DB::table($yearsInvoices->union($yearsExpenses))
            ->select(DB::raw('DISTINCT(year) as year'))
            ->pluck('year')->toArray();

This creates the union query (which will concatenate the two result tables), then uses the resulting union as the target for a new select which itself will select distinct years from this union resulting in (hopefully) all distinct years in each table

user_4878130

It turns out that the way you use DISTINCT doesn't work because Laravel will generate invalid SQL syntax. Try something like this:

$yearsInvoices = ModelOne::query()
      ->join(modeltwo_table, modeltwo_table.c_id,'=', c_id)
      ->select('YEAR(date)) as modelone_year', 'YEAR(modeltwo_table.date)) as modeltwo_year')
      ->distinct()
      ->groupBy('modelone_year')
      ->groupBy('modeltwo_year')
      ->pluck('modeltwo_year', 'modelone_year')
      ->toArray();

Just after writing this answer I realized that there was probably a simpler way, try this:

 $yearsInvoices = ModelOne::query()
      ->join(modeltwo_table, modeltwo_table.c_id,'=', c_id)
      ->select(
          DB::raw('DISTINCT YEAR(modelone_table.date) as modelone_year, YEAR(modeltwo_table.date) as modeltwo_year'))
      ->groupBy('modelone_year')
      ->groupBy('modeltwo_year')
      ->pluck('modeltwo_year', 'modelone_year')
      ->toArray();
user_19438080
Related