CODEWITHSUNDEEP
sqlsilverstripedatalist
Rubytastic
Rubytastic

Reputation: 15501

silverstripe query does not work, best way to debug?

I have this silverstripe query that does not work ( it outputs all messages and not the ones with the date range )

What would be the best way to tackle this query? Im fairly new to silverstripe and havent been able to find information on how to print the raw query.

return = Message::get()
            ->filter(array(
                'IsPublished' => true,
                'StartPublication:LessThanOrEqual' => date('Y-m-d'),
                'Priority' => array('High', 'Normal')
            ))
            ->where("\"StopPublication\" >= ".date('Y-m-d')." OR \"StopPublication\" IS NULL")
            ->sort('StartPublication', 'DESC')->limit($this->getLimit());

Upvotes: 1

Views: 838

Answers (2)

user4977453
user4977453

Reputation: 96

The correct answer is to not use where() - this is a trap method that a lot of learners fall into (presumably due to the name). It's intended basically only for very complex things that the ORM just can't handle.

You're calling filter at least, which is the correct thing. But what you want instead of where() is filterAny():

Message::get()
    ->filter([
        'IsPublished' => true,
        'StartPublication:LessThanOrEqual' => 'now',
        'Priority' => ['High', 'Normal']
    ])
    ->filterAny([
        'StopPublication:GreaterThanOrEqual' => 'now',
        'StopPublication' => null
    ])
    ->sort('StartPublication', 'DESC')
    ->limit($this->getLimit());

As the other answer already specifies, do not use an = on the return (or put a $ in front of return to make it a variable), and to return the query itself use $datalist->sql() http://api.silverstripe.org/3.1/class-DataList.html#_sql

But - seeing the docs on SQLQuery is wrong, because you're not using SQLQuery. You're using the ORM, so this doc page is far more relevant: http://docs.silverstripe.org/en/3.1/developer_guides/model/data_model_and_orm/#filterany

Upvotes: 2

Olli Tyynelä
Olli Tyynelä

Reputation: 576

For starts return = Message::get() its just return Message::get()

I assume that you have set php error reporting so that it outputs errors and SS is also in development mode so it won't hide error outputs.

The answer to your question is to to do either:

to output it to the output html:

Debug::dump(Message::get()
            ->filter(array(
                'IsPublished' => true,
                'StartPublication:LessThanOrEqual' => date('Y-m-d'),
                'Priority' => array('High', 'Normal')
            ))
            ->where("\"StopPublication\" >= ".date('Y-m-d')." OR \"StopPublication\" IS NULL")
            ->sort('StartPublication', 'DESC')->limit($this->getLimit())->sql());

or output it to the project roots log file

Debug::log(Message::get()
            ->filter(array(
                'IsPublished' => true,
                'StartPublication:LessThanOrEqual' => date('Y-m-d'),
                'Priority' => array('High', 'Normal')
            ))
            ->where("\"StopPublication\" >= ".date('Y-m-d')." OR \"StopPublication\" IS NULL")
            ->sort('StartPublication', 'DESC')->limit($this->getLimit())->sql());

See http://docs.silverstripe.org/en/developer_guides/model/sql_query/

Upvotes: 1

Related Questions