Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Simple Query to display data from table


Go to End
Reply


9 Posts   5009 Views

Avatar
Ronin

Community Member, 50 Posts

26 August 2009 at 10:19am

Having now got data from a table successfully displaying on the page I now want to go to the next step of getting the data from the table via a query. This will enable me to apply rules such as selection criteria etc to control the data that is returned from the table.

I have a lot of experience in the use of SQL in database environments just not how to do it within SilverStripe. I have done the tutorials and read everything I can find. So far the things that I have tried have not worked.

Some help on how you get the data to the page via a query would be greatly appreciated.

Avatar
Hamish

Community Member, 712 Posts

26 August 2009 at 2:00pm

Edited: 26/08/2009 2:01pm

If you've done [url=http://doc.silverstripe.org/doku.php?id=tutorial:3-forms]tutorial 3[/url] you should have a pretty good idea.

Can you post an example of what hasn't worked?

Using an example from [url=http://silverstripe.org/general-questions/show/266101?start=0#post266167#post266167]one of your previous threads[/url]:

public function MyThing()
{
   return DataObject::get("TableName");
}

In the template:

<ul>
   <% control MyThing %>
      <li>This is the Title: $Title</li>
   <% end_control %>
</ul>

All you need to do is modify the "DataObject::get('TableName')" line with extra arguments in accordance with the [url=http://doc.silverstripe.org/doku.php?id=datamodel]Data Model[/url]

Eg

return DataObject::get("TableName", "ID > 5", "Name ASC");

Avatar
Ronin

Community Member, 50 Posts

26 August 2009 at 10:06pm

I have got the example you show working. However this is simply displaying all of the data from the table. The next step for me is access the table via a query rather than directly so that I can control which fields I retrieve and what criteria I use. So I want the syntax that allows me to build a query and display the results on a page.

Avatar
Hamish

Community Member, 712 Posts

27 August 2009 at 8:33am

SilverStripe uses an ORM to provide access to data. You don't normally have to construct a query - instead, you access objects via their relations. Once again, please refer to the [url=http://doc.silverstripe.org/doku.php?id=datamodel]data model[/url].

If you really need a query, you can use [url=http://doc.silverstripe.org/doku.php?id=sqlquery]SQLQuery[/url], but you shouldn't need to use it for most operations.

Could you explain what you are trying to do a bit more specifically?

Avatar
Ronin

Community Member, 50 Posts

27 August 2009 at 9:17am

Hi Hamish,
Thanks for you patience. I understand that SS provides mechanisms that mean that you don't have to be an SQL expert. So far this is what I have tried.

function SayingOfDay() {
$sqlQuery = new SQLQuery();
$sqlQuery->select = array(
'Author',
'Saying'
);
$sqlQuery->from = "Sayings";

// $sqlQuery->having = "";
// $sqlQuery->orderby = "";
// $sqlQuery->limit = "";
// $sqlQuery->distinct = true;

// get the raw SQL
$rawSQL = $sqlQuery->sql();

// execute and return a Query-object
$result = $sqlQuery->execute();

return $result;
//return DataObject::get($result);
//return DataObject::get("Sayings");
}

When flushing the system it comes up with a lot of errors but down the bottom it reports

user_error(DATABASE ERROR: Couldn't run query: SELECT Name, Application FROM | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1,256)
line 431 of Database.php

Database->databaseError(Couldn't run query: SELECT Name, Application FROM | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1,256)
line 108 of MySQLDatabase.php

MySQLDatabase->query(SELECT Name, Application FROM ,256)
line 81 of DB.php

DB::query(SELECT Name, Application FROM )
line 157 of SQLQuery.php

SQLQuery->execute()
line 67 of Page.php

To me this indicates that for some reason I'm not specifying the table correctly. However this is the table that I have successfully used to display data on the page using the technique that you reference in the beginning of this topic.

Avatar
Ronin

Community Member, 50 Posts

1 September 2009 at 8:43pm

If any body out there can help me I'd really appreciate it. I want to learn how to use the tools that SS provides to build queries. I am able to return data from tables including apply filters. Now I want to understand how to achieve the same thing using the querying faciltities of SS. In my previous post I have showed what I have tried so far. It seems to me that there are two parts to the problem correctly using the querying facilities and then returning the data to the appropriate page.

Avatar
ajshort

Community Member, 244 Posts

1 September 2009 at 9:19pm

Edited: 01/09/2009 9:19pm

It would help if you actually posted what you are trying to achieve - why do you need to circumvent the ORM and access the SQLQuery object directly? You do know that they basically do the same thing (get data from the database), but the ORM through DataObject::get() etc. will wrap it in classes before returning it. There are very few use cases where you should need to manually construct a query, and in fact it should be avoided.

Avatar
Ronin

Community Member, 50 Posts

2 September 2009 at 12:02pm

Thanks for your reply. I'm not trying to be difficult or obscure.
I dont have a real problem that I am trying to solve right now.
What I'm trying to do is learn the correct techniques within SS to creae what I would traditionally calls queries from the work that I normally do for clients so that when I do have specific problems to solve I already understand how to do it.
First I want to be able to return the data from a single table using complex criteria, sorting and grouping options.
Once I've got that working I want to be able to link multiple tables together and use similar options as above.
I'm very happy to do it using the method that you say I should. I just need to know what the syntax is to achieve this including exactly what is needed to return the data to the page.

I am currently successfully using the following code on my initial site
public function SayingOfDay() {
return DataObject::get("Sayings", "Author <> 'zzzz'", "Author ASC");
}
It sounds like your suggesting that this is the route that I should take however I cant see how this handle's the more complex situations that I know I will be needing to address in the near future.

Go to Top