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

How to speed up these queries?


Go to End


12 Posts   3309 Views

Avatar
Mr. Neave

Community Member, 23 Posts

13 January 2015 at 10:57pm

This is a data model from a site I built a few years back and am migrating to SS 3.1.8.

Artist.
Artwork. Has many Artists.
Exhibition. Has many Artworks.

There are two queries that are really slow because I’m a much better designer than I am a programmer. It gets very slow because the second method runs a loop, repeatedly calling the first method, which in turn runs its own loop… so the site is pretty fast apart from any page that uses the output of these functions.

Can these either be written to be faster (perhaps by getting rid of the ugly loops somehow), or could the results of the queries be cached somehow?

Get the artists in an exhibition by doing the following in Exhibition.php:

    public function Artists() {
        $artists = new ArrayList();
        $artworks = $this->Artworks();
        // loop through artworks and retrieve each related artist.
        // return a dataobjectset that contains each artist only once.
        foreach($artworks as $artwork) {
            $next_artists = $artwork->Artists();
            if($next_artists) {
                foreach($next_artists as $check_artist) {
                    if(!$artists->byID($check_artist->ID)) {
                        $artists->push($check_artist);
                    }
                }
            }
        }
        return $artists;
    }

Get every Exhibition an Artist has been in by doing this in Artist.php:

    public function Exhibitions() {
        $return = new ArrayList();
        $exhibitions = DataObject::get('Exhibition');
        foreach($exhibitions as $exhibition) {
            $artists = $exhibition->Artists();
            if($artists->byID($this->ID) && !$return->byID($exhibition->ID)) {
                $return->add($exhibition);
            }
        }
        return $return;
    }

Avatar
martimiz

Forum Moderator, 1391 Posts

14 January 2015 at 3:48am

I'm not sure I get this right: Artwork has_many Artists... Do you mean multiple artists work together on one piece of art, say one painting?

Avatar
Mr. Neave

Community Member, 23 Posts

15 January 2015 at 10:15pm

Yes, some of the artworks are collaborations between several artists.

Avatar
martimiz

Forum Moderator, 1391 Posts

17 January 2015 at 12:55am

Edited: 17/01/2015 12:56am

Hi

This could probably be done in one single query, and I'd like to help but first I'd have to get your relations clear. This is what you say:

Artist.
Artwork. Has many Artists.
Exhibition. Has many Artworks.

Which means: exhibitions have many artworks, artworks in turn have many artists, and the other way around: each artist has one artwork only and each artwork has one exhibition only, which can't be true?

I assume it is something like this?

Exhibition many_many Artworks / Artwork belongs_many_many Exhibitions (Same artworks can be displayed on multiple exhibitions)
Artist many_many Artworks / Artwork belongs_many_many Artists (as multiple artists sometimes work on one piece of art)

Avatar
Mr. Neave

Community Member, 23 Posts

17 January 2015 at 1:24pm

Thank you for your replies martimiz.

And you are right, I didn’t explain that well. This how the relations actually work:

Artist:

    private static $belongs_many_many = array (
		'Artworks' => 'Artwork'
    );

Artwork

    private static $many_many = array (
		'Artists' => 'Artist'
    );
    private static $belongs_many_many = array (
		'Exhibitions' => 'Exhibitions'
    );

Exhibition:

    private static $many_many = array (
		'Artworks' => 'Artwork'
    );

And to be precise about what the functions should do:

Exhibition -> Artists()
Get a list of artists featured in the exhibition, by looking at the artists that belong to each artwork in the exhibition. Each artist should only appear once in the list that gets returned.

Artist -> Exhibitions()
Get a list of all of the exhibitions that the artist has been featured in, by finding all of the exhibitions that have artworks by the artist.

Hopefully that makes more sense. Thanks again.

Avatar
Mr. Neave

Community Member, 23 Posts

17 January 2015 at 1:24pm

Edited: 17/01/2015 1:24pm

Thank you for your replies martimiz.

And you are right, I didn’t explain that well. This how the relations actually work:

Artist:

    private static $belongs_many_many = array (
		'Artworks' => 'Artwork'
    );

Artwork:

    private static $many_many = array (
		'Artists' => 'Artist'
    );
    private static $belongs_many_many = array (
		'Exhibitions' => 'Exhibitions'
    );

Exhibition:

    private static $many_many = array (
		'Artworks' => 'Artwork'
    );

And to be precise about what the functions should do:

Exhibition -> Artists()
Get a list of artists featured in the exhibition, by looking at the artists that belong to each artwork in the exhibition. Each artist should only appear once in the list that gets returned.

Artist -> Exhibitions()
Get a list of all of the exhibitions that the artist has been featured in, by finding all of the exhibitions that have artworks by the artist.

Hopefully that makes more sense. Thanks again.

Avatar
martimiz

Forum Moderator, 1391 Posts

20 January 2015 at 6:31am

Edited: 20/01/2015 6:58am

This is what I got to work to get all artists for a certain exhibition (note: I used a Page to represent the exhibition, but the idea remains the same):

return Artist::get()
		->innerJoin('Artwork_Artists', 'Artist.ID = Artwork_Artists.ArtistID')
		->innerJoin('Artwork', 'Artwork_Artists.ArtworkID = Artwork.ID')
		->filter(array('Artwork.PageID' => $this->ID));

So with Exhibition many_many Artwork many_many Artist that might become something like this (but I didn't test it):

return Artist::get()
		->innerJoin('Artwork_Artists', 'Artist.ID = Artwork_Artists.ArtistID')
		->innerJoin('Artwork', 'Artwork_Artists.ArtworkID = Artwork.ID')
		->innerJoin('Exhibition_Artworks', 'Artwork.ID = Exhibition_Artworks.ArtworkID')
		->innerJoin('Exhibition', 'Exhibition_Artworks.ExhibitionID = Exhibition.ID')
		->filter(array('Exhibition.ID' => $this->ID));

Using innerJoin will not return any fields from the joined tables, so all you get is Artist fields. And the exact opposite route should work for Artist -> Exhibition... (from within the Artist class)

[EDIT] Oops I forgot: for security reasons the 'on' statement in the innerJoin should be encased in double quotes like so:

		...
		->innerJoin('Artwork_Artists', '"Artist"."ID" = "Artwork_Artists"."ArtistID"')

Avatar
Mr. Neave

Community Member, 23 Posts

20 January 2015 at 9:18pm

Brilliant!

So, this is returning a list of artists, where they are related to any artwork which is in turn related to any exhibition. (At that seems to be what’s happening here, is it?).

return Artist::get()
		->innerJoin('Artwork_Artists', 'Artist.ID = Artwork_Artists.ArtistID')
		->innerJoin('Artwork', 'Artwork_Artists.ArtworkID = Artwork.ID')
		->innerJoin('Exhibition_Artworks', 'Artwork.ID = Exhibition_Artworks.ArtworkID')
		->innerJoin('Exhibition', 'Exhibition_Artworks.ExhibitionID = Exhibition.ID')

But, the last line, where it looks like we filter the results against the current exhibition ID, removes all of the artists from the list completely — is it filtering the joined artists from the previous four lines, rather than the rather than the Exhibition_Artworks table?

->filter(array('Exhibition.ID' => $this->ID));

Somewhere in there it looks like we need to filter the Exhibition_Artworks table by $this->ID first, before we join the artists with it (right?). What’s the syntax for that?

Go to Top