CODEWITHSUNDEEP
ormsilverstripe
Zach Smith
Zach Smith

Reputation: 8971

Using SilverStripe's SQLQuery object without selecting * fields

Looking at the documentation, there are fairly good instructions on how to build up a SQL query.

My code looks like this:

$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('PropertyPage')->selectField('PropertyType')->setDistinct(true);

I'm aiming to get the following SQL: 

SELECT DISTINCT PropertyType FROM PropertyPage

but instead I'm getting back this: 

SELECT DISTINCT *, PropertyType FROM PropertyPage

Even their own example seems to give back a 'SELECT DISTINCT *. How can I avoid this?

Upvotes: 2

Views: 279

Answers (2)

Turnerj
Turnerj

Reputation: 4288

Just to add to @wmk's answer as well as directly address how to do it with SQLQuery, your call to selectField is the reason why that query happened. The documentation to selectField shows that it adds an additional field to select, not restrict the list to just that field.

The reason why their examples (A and B) also had the issue is that the first parameter for the constructor for SQLQuery is a default value of "*" for select statement.

To still use your code as a base, replace your use of selectField with setSelect. Your query will then look like this:

SELECT DISTINCT PropertyType FROM PropertyPage

It isn't bad to directly query the database using SQLQuery especially if you really just want the raw data of a particular column or the result itself cannot be expressed against a DataObject (eg. using column aliases). You would also have a small performance improvement that PHP would not have to instantiate a DataObject.

While saying that, it can be far more useful having a DataObject and the various functions that it exposes per record.

Upvotes: 3

wmk
wmk

Reputation: 4626

Why do you use SQLQuery directly?

With pure ORM it should go like this:

$result = PropertyPage::get()->setQueriedColumns(array('PropertyType'))->distinct();

which returns a DataList you can loop over.

See setQueriedColumns() and distinct()

What version of SS framework are you using? Distinct was added in 3.1.7 iirc.

Upvotes: 3

Related Questions