Where Query - using calculated date value from record in the DB

I think this will have to be done through SQL with raw. Not sure if this will work but worth a try:

$tests = Test::active()->whereRaw("start_date = DATE_SUB(NOW(), INTERVAL {{column_name}} DAYS)")

make sure to replace {{column_name}} with the column where you store the days

Thanks for the quick reply.

The above throws a general error which is:

General error: 1 near "variation_length": syntax error (SQL: select * from "tests" where "published_at" is not null and "completed_at" is null and "visible" = 1 and start_date = DATE_SUB(NOW(), INTERVAL variation_length DAYS))

INTERVAL variation_length DAY

try that

also DATE(DATE_SUB(NOW(), INTERVAL variation_length DAY)))

to remove the hour minute second

This throws a similar error:

 General error: 1 near "variation_length": syntax error (SQL: select * from "tests" where "published_at" is not null and "completed_at" is null and "visible" = 1 and start_date = DATE(DATE_SUB(NOW(), INTERVAL variation_length DAY))))

Would it be possible using some extension of parameter grouping do you know? E.g:

Test::active()->where(function($query)
            {
                $query->where('variation_length', '>', $carbonDate->subDays('VARIATION_LENGTH_VALUE');
            })
            ->get();

Either you have a different type of database or you may have one brace too many in the raw part, can you check that? Because i tried it myself and it should work.

$tests = Test::active()->->whereRaw("start_date = DATE(DATE_SUB(NOW(), INTERVAL {{column_name}} DAYS))")

I can't see any errors in it. My exact code is below:

$tests = Test::active()->whereRaw("start_date = DATE(DATE_SUB(NOW(), INTERVAL variation_length DAY))")->get();

The DB type is InnoDB with UTF-8 Unicode encoding.

I still get the general error as before. Do you see anything obvious?

can you try datesub on its own? SELECT DATE_SUB(NOW(), INTERVAL 1 DAY)

The same error pretty much :(

try subdate instead of datesub

SELECT SUBDATE(NOW(), INTERVAL 1 DAY)

 General error: 1 near "SELECT": syntax error (SQL: select * from "tests" where "published_at" is not null and "completed_at" is null and "visible" = 1 and start_date = SELECT SUBDATE(NOW(), INTERVAL 1 DAY))

Seems like the same kind of error. I'm not sure what the problem is but it seems like a fundamental problem with the subdate function in my db :/

hold on I meant trying SELECT on its own so we can figure out what would happen

Do you have mysql workbench or phpmyadmin?

If not then try this

dd(DB::statement("SELECT SUBDATE(NOW(), INTERVAL 1 DAY)"));

also try this:

$tests = Test::active()->whereRaw("start_date = DATE(NOW())")->get();

if that passes, try this $tests = Test::active()->whereRaw("start_date = DATE(SUBDATE(NOW(), INTERVAL 1 DAY))")->get();

Ok so maybe we're getting somehwere.

It seems like it isn't getting access to the raw sql functions.

I entered

$tests = Test::active()->whereRaw("start_date = DATE(NOW())")->get();

and got an error saying the NOW function doesn't exist

General error: 1 no such function: NOW (SQL: select * from "tests" where "published_at" is not null and "completed_at" is null and "visible" = 1 and start_date = DATE(NOW()))

are you using sqlite?

Nope, MySQL.

Thanks for taking the time to help btw. Much appreciated! :)

https://www.google.com/search?client=ubuntu&channel=fs&q=%22General+error%3A+1+no+such+function%3A+NOW%22&ie=utf-8&oe=utf-8

Never seen this kind of error, but it seems to be connected to sqlite, but I may be wrong. Good luck figuring it out

ah... does Laravel use SQL lite when using refresh DB for testing do you know?

I'm using TDD and running the code via the test so that could explain it?!

yes, tests are usually run through sqlite

You could extract this logic into its own class, make an interface and then have different implementations for MySQL and SQLite, but this is more advanced.

Else if you don't expect many records for this table, you could try filtering them through PHP

try this, not tested

$now = CarbonImmutable::parse(now())->format('d/m/y');
$tests = Test::active()->where('start_date', '=', $now->subDays('DB_COLUMN_VAL'));

whereDate() allows to check for "date (month day) part of your date". Similar methods: whereMonth() and whereYear().

Just use plain old where() in your query: Test::active()->where('start_date', $now->subDays('DB_COLUMN_VAL'));

@arushad @pilat The DB_COL_VAL is another column on the table row - its not something you can manipulate with PHP so neither answer helps.

set DB_COLUMN_VAL to a variable then, replace the 'DB_COLUMN_VAL' with that variable

@arushad - its not really possible to do that as the DB_COLUMN_VAL will differ per row in the DB and I need to use that value to filter the results.