Jump to:

22977 Posts in 11667 Topics by 2827 members

General Questions

SilverStripe Forums » General Questions » Simple Query to display data from table

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

Page: 1 2
Go to End
Author Topic: 4566 Views
  • Ronin
    Avatar
    Community Member
    50 Posts

    Simple Query to display data from table Link to this post

    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.

  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Simple Query to display data from table Link to this post

    If you've done tutorial 3 you should have a pretty good idea.

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

    Using an example from one of your previous threads:

    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 Data Model

    Eg

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

  • Ronin
    Avatar
    Community Member
    50 Posts

    Re: Simple Query to display data from table Link to this post

    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.

  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Simple Query to display data from table Link to this post

    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 data model.

    If you really need a query, you can use SQLQuery, but you shouldn't need to use it for most operations.

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

  • Ronin
    Avatar
    Community Member
    50 Posts

    Re: Simple Query to display data from table Link to this post

    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.

  • Ronin
    Avatar
    Community Member
    50 Posts

    Re: Simple Query to display data from table Link to this post

    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.

  • ajshort
    Avatar
    Community Member
    244 Posts

    Re: Simple Query to display data from table Link to this post

    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.

  • Ronin
    Avatar
    Community Member
    50 Posts

    Re: Simple Query to display data from table Link to this post

    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.

    4566 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.