Tudor Ciotlos Tudor Ciotlos - 7 months ago 6
SQL Question

Using Laravels Eloquent with database views

I have two database tables, expenses and incomes and I created a MySQL view, transactions, which basically is an UNION between the two tables, selecting the fields I am interested in.

The transactions view contains the following columns:


  • transaction_id

  • user_id

  • amount

  • note

  • transaction_type

  • updated_at

  • created_at



I created an Eloquent Model for the view, called Transactions.

The problem is when I want to retrieve transactions based on certain criteria.
E.g. I want to get all transactions for a User. Normally, if transactions was a table, I would define a foreign key relationship and I would simply call
$user->hasMany('App\Models\Transaction')
.

Since foreign keys are not possible for views, I tried using the 'where' method:
Transaction::where('user_id', $user->id)
. This query does not return anything, neither do any other queries I tested. The only method that returned data is
Transaction::all()
, but this doesn't help me very much.

What am I doing wrong?

Answer

For where statement in Eloquent you must to end up with get, try this:

$transactions = Transaction::where('user_id', $user->id)->get();
Comments