Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Custon SQL and DataList (SS3)

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

Page: 1
Go to End
Author Topic: 1440 Views
  • mishell
    Avatar
    Community Member
    19 Posts

    Custon SQL and DataList (SS3) Link to this post

    Hi, in SS 2.x.x I had no problems with custom queries transformed to Dataobjectsets. I have made

    $result = DB::query('SELECT blah blah blah');

    $dos = singleton('WowClass')->buildDataObjectSet($result);

    if($dos)
    $dos->someMagicFunctionToSetLimit(0,40); // can't find that one, but it was neat ;)

    So this is SS 2.x.x

    I wanna do something like this in SS3 ;)

    I have:

    $result = DB::query('
    SELECT * FROM (
    SELECT * FROM (
    SELECT * FROM Auction WHERE IsFeatured = 1
    ORDER BY ID DESC
    ) as Auction
    UNION ALL
    SELECT * FROM(
    SELECT * FROM Auction WHERE IsFeatured = 0
    ORDER BY ID DESC
    ) as Auction
    ) as Auction
    ');

    singleton('Auction')->buildDataObjectSet(....) // Here is deprecated...

    How can I make this work under SS3?

    Any help will be appreciated
    M.

  • ajshort
    Avatar
    Community Member
    244 Posts

    Re: Custon SQL and DataList (SS3) Link to this post

    Perhaps I'm misunderstanding what you're trying to achieve, but couldn't you just do:

    $auctions = DataList::create('Auction')->limit(40)->sort(array(
       'IsFeatured' => 'DESC',
       'ID' => 'DESC'
    ));

  • mishell
    Avatar
    Community Member
    19 Posts

    Re: Custon SQL and DataList (SS3) Link to this post

    You misunderstood

    I wanna get all featured auctions sort desc union with not featured auctions sort desc and paginate all of them

    Thank You for replay

  • ajshort
    Avatar
    Community Member
    244 Posts

    Re: Custon SQL and DataList (SS3) Link to this post

    You're way over complicating things - all you need to do is sort by "IsFeatured" first - this will have all the featured auctions first, and then sort secondly by "ID". You don't need to worry about any of the UNION stuff.

    Also the method for paginating stuff has changed in SS3 - all you need to do is wrap the data list inside a PaginatedList instance.

  • mishell
    Avatar
    Community Member
    19 Posts

    Re: Custon SQL and DataList (SS3) Link to this post

    ok, maybe it's really early and coffee didn't get a kick, but. What I wanna achieve is get all featured auctions first and sort them, and show them. If I do what You wrote I will get mixed featured auctions with not featured sorted by date.

    Featured auctions

    1. Featured | 2011-04-04
    2. Featured | 2011-04-03
    3. Featured | 2011-04-02

    -----------

    Not Featured Auctions

    1. Not Featured | 2011-04-04
    2. Not Featured | 2011-04-03
    3. Not Featured | 2011-04-02

  • sktzoootech
    Avatar
    Community Member
    5 Posts

    Re: Custon SQL and DataList (SS3) Link to this post

    What Im interested in here is how are you gonna do a union of 2 sub sets using SS3 ORM. Something like a Datalist::create('table1')->union(Datalist::create('table2')). Anyone from the developers?

  • arielek
    Avatar
    Community Member
    1 Post

    Re: Custon SQL and DataList (SS3) Link to this post

    I have a problem. I would like write in ss DataList. search form is a method form action form.

    public function searchEvent(){
    $event= Event::get();
    $event= $event->filter(array('active' => '1','title' => 'title'));
    return $this->owner->customise($event)->renderWith(array('Template','Page'));
    }
    Template.ss code

    <% loop searchEvent %>
       $Title
    <% end_loop %>

    1440 Views
Page: 1
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.