Query Builder executes, no error, but no data (but can get data when query directly against db)

Here's my code:

        $records = DB::connection('dw-billing')
                        ->table('account_with_usage_no_zban')
                        ->select([
                            DB::connection('dw-billing')->raw("FROM_DAYS(date_id) as `date`"),
                            'contact_email',
                            'license_key'
                        ])
                        ->whereBetween('date_id',[DB::connection('dw-billing')->raw("TO_DAYS('".Carbon::parse($fromdate)->toDateString()."')"),DB::connection('dw-billing')->raw("TO_DAYS('".Carbon::parse($todate)->toDateString()."')")])
                        ->groupBy(['date','contact_email','license_key'])
                        ->get();

Which I've confirmed using a listener builds the following sql statement:

select FROM_DAYS(date_id) as `date`, `contact_email`, `license_key` from `account_with_usage_no_zban` where `date_id` between TO_DAYS('2017-04-25') and TO_DAYS('2017-04-25') group by `date`, `contact_email`, `license_key`

But I get no records returned.

If I execute the query directly via the mysql client, I get the records I'm expecting (3)

Any ideas?

jlrdw

9 years ago

Level 75

Query shows

(date_id) as `date`

but the where

where `date_id`

Is "date" a reserved word, I am not sure. Try the query with normal date, not carbon.

Just my 2 cents, for a library that's suppose to be so easy to use, I see tons of carbon questions on the forum. I'd just use it for things like difference for humans, etc, not date calculations.

chadhortonwowza

9 years ago

Level 1

I've confirmed the dates are strings. date is okay to use as long as it's wrapped in back-ticks. But just to test, I hardcoded the dates and changed the date to usage_date. I still get no results but the query executes and I get results when I use the mysql client.

Please or to participate in this conversation.