João Serra João Serra - 1 month ago 18
MySQL Question

Can't replicate a DATE_FORMAT query in Laravel

So, i have this query in written out in a different version of my project.

$result = $db->select("SELECT `nID`, `txNome`, `txEmail`
FROM `".$this->tablename."`
WHERE `at_state` != 'invalid'
AND `dtCreated` < :weekAgo
AND DATE_FORMAT(dtCreated, '%w') = DATE_FORMAT(NOW(), '%w')
AND `remarketing_amigos_sent` = '1'
AND `amigo` IS NULL
AND `txEmail` NOT IN (
SELECT `email`
FROM `utils`.`unsubscribe`
WHERE `email` = `".$this->tablename."`.`txEmail`
AND (`idCampaign` = :campaign OR `idCampaign` = '*')
);
", array(
'campaign' => CoreHelper::getCampaignID(),
'weekAgo' => $aWeekAgo
));


And i'm trying to convert it for Laravel's Query Builder, i can convert the whole thing except for that DATE_FORMAT bit... I have no idea how to convert that into Eloquent.

The Application should sent e-mails once a week to a list of people, that's why the DATE_FORMAT is there, to match the weekdays.
How can i convert this into Query Builder?

My current code looks like this

$this->lead->where('at_state', '!=', 'invalid')
->where('dtCreated', '<', $aWeekAgo)
->where(???)
->where('remarketing_amigos_sent', '1')
->whereNull('amigo')
->whereNotIn('txEmail', function($q) {
$q->select('email')->from('utils.unsubscribe')->where('idCampaign', CoreHelper::getCampaignID());
})->get();


Note that the $this->lead section is intentionally different because it refers to my Eloquent Model, whereas the original version was done using the DB Facade.

Answer

You can use Laravel's whereRaw() function to accomplish some complex queries as:

->whereRaw("DATE_FORMAT(dtCreated, '%w') = DATE_FORMAT(NOW(), '%w')")