stevegoddard14's avatar

Agreggate With Join - The attribute [id] either does not exist or was not retrieved for model

My company sells widgets on the internet. The widgets are sold on a number of sites including our own website, eBay, Amazon etc. I have a simple invoice table and a linked table - invoice_sources. An invoice belongsTo a souce (eBay, Amazon, etc).

I'm trying to return a summary of the number of invoices (within a date range) from each source.

This is my query:

$invoices_by_source = Invoice::
      select(DB::raw('count(invoices.id) as invoice_count, invoices.invoice_source_id'))
    ->join('invoice_sources', 'invoices.invoice_source_id', '=', 'invoice_sources.id')
    ->where('date', '>=', $date_start)
    ->where('date', '<=', $date_end)
    ->groupBy('invoices.invoice_source_id')
    ->orderBy('invoice_sources.short')
    ->get();
return dd($invoices_by_source);

However, I get the following error message when I dd the query:

The attribute [id] either does not exist or was not retrieved for model [App\Models\Invoice].

I'm a bit baffled. The invoices table includes an ID column. If I return the query toRawSql() I get this query:

select
    count(invoices.id) as invoice_count,
    invoices.invoice_source_id
from
    `invoices`
    inner join `invoice_sources` on `invoices`.`invoice_source_id` = `invoice_sources`.`id`
where
    `date` >= '2023-12-01 00:00:00'
    and `date` <= '2023-12-01 23:59:59'
group by
    `invoices`.`invoice_source_id`
order by
    `invoice_sources`.`short` asc

This query runs fine in TablePlus.

Any suggestions?

Thanks, Steve

0 likes
5 replies
LaryAI's avatar
LaryAI
AI
2 years ago
Level 58

Hello Steve,

It seems like you're encountering an issue with Eloquent trying to hydrate models with only a subset of the necessary attributes. When you're using groupBy and selecting specific columns, Eloquent expects that the primary key (id in this case) is also selected, or else it will throw an error when trying to create models from the result set.

To solve this, you can either include the id in the select statement or, if you don't need Eloquent models and just want an array of results, you can use the get() method with the ->toArray() to avoid model hydration.

Here's how you can adjust your query:

$invoices_by_source = Invoice::query()
    ->select(DB::raw('count(*) as invoice_count, invoice_sources.short'))
    ->join('invoice_sources', 'invoices.invoice_source_id', '=', 'invoice_sources.id')
    ->where('date', '>=', $date_start)
    ->where('date', '<=', $date_end)
    ->groupBy('invoices.invoice_source_id', 'invoice_sources.short')
    ->orderBy('invoice_sources.short')
    ->get()
    ->toArray(); // Convert the collection to an array

return dd($invoices_by_source);

Note that I've added invoice_sources.short to the groupBy clause because you're ordering by it, and SQL standards require that all selected columns in an aggregate query that are not aggregated should be included in the GROUP BY clause.

If you do need the id for some reason, you can include it in the select statement, but be aware that it won't make sense to have it there since you're grouping by invoice_source_id and thus there will be multiple id values for each group:

$invoices_by_source = Invoice::query()
    ->select(DB::raw('count(*) as invoice_count, invoice_sources.short, invoices.invoice_source_id'))
    ->join('invoice_sources', 'invoices.invoice_source_id', '=', 'invoice_sources.id')
    ->where('date', '>=', $date_start)
    ->where('date', '<=', $date_end)
    ->groupBy('invoices.invoice_source_id', 'invoice_sources.short')
    ->orderBy('invoice_sources.short')
    ->get();

return dd($invoices_by_source);

In this case, the id column will be included, but it will be the id of the last invoice in each group, which may not be meaningful.

I hope this helps you resolve the issue. Let me know if you have any further questions.

Best regards, LaracastsGPT

stevegoddard14's avatar

@LaryAI I appreciate your suggestions - i can see where you're coming from - but neither of your suggestions work. But it has given me a starting point for some more googling...

stevegoddard14's avatar

Ok - found the solution (with a bit of prompting from @laryai . For this query, i can't run the query against the model - I have to use DB::table(). My final query is (note the DB:table and the get() - no need for toArray()):

$invoices_by_source = DB::table('invoices')
    ->select(DB::raw('count(*) as invoice_count, invoice_sources.short'))
    ->join('invoice_sources', 'invoices.invoice_source_id', '=', 'invoice_sources.id')
    ->where('date', '>=', $date_start)
    ->where('date', '<=', $date_end)
    ->groupBy('invoices.invoice_source_id', 'invoice_sources.short')
    ->orderBy('invoice_sources.short')
    ->get();
return dd($invoices_by_source);

The Laravel docs do have this in the example. But there is nothing in the docs to indicate that you have to use DB::table() rather than Model::query().

1 like
stevegoddard14's avatar

I know I asked this some time ago... but I finally figured out the underlying issue. The Invoice model had a number of relationships defined. That's fine - however, in the model I had defined some of them to ALWAYS be included in results using the $with array. This causes a problem because when querying the Invoice model and doing COUNT() or SUM(), the id field needed for the joins was not included and broke the query when Laravel tried to query the relationships.

So the simple solution was to add setEagerLoads([]) at the start of the query to override the $with array. For instance (similar query):

$query = Invoice::
      setEagerLoads([])
    ->selectRaw('COUNT(invoices.id) AS invoices_count')
    ->leftJoin('customer_addresses', 'invoices.delivery_address_id', '=', 'customer_addresses.id')
    ->whereBetween('invoices.date', [$this->date_from, $this->date_to])
    ->where('customer_addresses.country', 'like', $this->country);
    ->first();

Hope that helps someone.

Please or to participate in this conversation.