Using SQL Functions in eloquent select statement

Question

I am using Laravel Framework 6.16.0.

I want to select data via sql functions, such as CONCAT('Q', QUARTER(transaction_date)).

However, I get an error:

Column not found: 1054 Unknown column 'CONCAT('Q', QUARTER(transaction_date))' in 'field list'

The query looks like the following:

$days = 365 * 2; // two years

$trx = DB::connection('mysql_prod')->table('product')->select(array("transaction_date", "CONCAT('Q', QUARTER(transaction_date))", "YEAR(transaction_date)", "trx_type", "amount_range", "transaction_value", "symbol"))
    ->leftJoin('companies', 'product.companies_id', '=', 'companies.id')
    ->leftJoin('persons', 'persons.id', '=', 'product.persons_id')
    ->where('companies.symbol', '=', $s->symbol)
    ->where('persons.person_type', '=', 'manager')
    ->whereDate('transaction_date', '>=', Carbon::now()->subDays($days))
    ->orderBy('transaction_date', 'desc')
    ->get();

Any suggestion how to use SQL-functions within an eloquent select statement?

Appreciate your reply!

you would need to use a DB::raw for the CONCAT stuff as it thinks you are trying to select 'columns' with select — lagbox
– lagbox, Commented Dec 16, 2020 at 22:15

Some Guy · Accepted Answer · 2020-12-17 05:44:28Z

1

As @lagbox stated in his comment,

You should change "CONCAT('Q', QUARTER(transaction_date))" to DB::raw("CONCAT('Q', QUARTER(transaction_date))")

$days = 365 * 2; // two years

$trx = DB::connection('mysql_prod')->table('product')->select(array("transaction_date", DB::raw("CONCAT('Q', QUARTER(transaction_date))"), "YEAR(transaction_date)", "trx_type", "amount_range", "transaction_value", "symbol"))
    ->leftJoin('companies', 'product.companies_id', '=', 'companies.id')
    ->leftJoin('persons', 'persons.id', '=', 'product.persons_id')
    ->where('companies.symbol', '=', $s->symbol)
    ->where('persons.person_type', '=', 'manager')
    ->whereDate('transaction_date', '>=', Carbon::now()->subDays($days))
    ->orderBy('transaction_date', 'desc')
    ->get();

answered Dec 17, 2020 at 5:44

Some Guy

1269 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SQL Functions in eloquent select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related