Alphonse Alphonse - 11 days ago 7
MySQL Question

Laravel query to compare two strings from database with a date of format Y-m

So I have a table in my database core_companies. In this table I have a column settings which is of type json.
In it I store credit cards for each company. It looks like this:

settings->credit_cards[[0] => {expiration_date: {year: "17", month: "04"}]

I have two datepickers from which I get in php two dates like this:
"2017-04"
.
What I try to do is return companies whose credit cards expire within a give period, like this (which from obvious reasons doesn't work - it simply ignores my query and returns all companies):

if ($key == "expiration_date->date_from" && $value) {
$companies = $companies->where("settings->credit_cards->expiration_date", '>=', $value);
}
if ($key == "expiration_date->date_to" && $value) {
$companies = $companies->where("settings->credit_cards->expiration_date", '<=', $value);
}


So the datepickers are stored in an object
expiration_date
. The first one selects the date from which the comparison starts (the date is stored in $value).
How can I make this query work? How can I compare a date of format Y-m with two separate string from database
{year: "17", month: "04"}
and return credit cards who expire within a given period of time?
Thank you all for your time!

Sam Sam
Answer

you can create parts of your value in date and month and compare with closure in where for ex.

if ($key == "expiration_date->date_from" && $value) {
    $parts = explode('-', $value);
    $companies = $companies->where(function($q) use($parts){
        // either year is greater or year is same and month is same or greater
        $q->where("settings->credit_cards->expiration_date->year", '>', $parts[0])
            ->orWhere(function($q) use($parts){
                $q->where("settings->credit_cards->expiration_date->month", '>=', $parts[1])
                    ->where("settings->credit_cards->expiration_date->year", '=', $parts[0]);
            });
    });
}

same goes for date_to filter