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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

Custon SQL and DataList (SS3)


Go to End


7 Posts   3308 Views

Avatar
mishell

Community Member, 19 Posts

28 February 2012 at 9:31am

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.

Avatar
ajshort

Community Member, 244 Posts

28 February 2012 at 5:53pm

Edited: 28/02/2012 5:54pm

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'
));

Avatar
mishell

Community Member, 19 Posts

28 February 2012 at 11:35pm

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

Avatar
ajshort

Community Member, 244 Posts

28 February 2012 at 11:50pm

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.

Avatar
mishell

Community Member, 19 Posts

29 February 2012 at 12:00am

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

Avatar
sktzoootech

Community Member, 6 Posts

8 January 2013 at 5:02pm

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?

Avatar
arielek

Community Member, 1 Post

20 February 2013 at 9:21am

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 %>