Optimisation of code

[arjen]

Hi!

I have a question about optimizing some code. It's about a site which has books and authors. The structure is like this:

- Books

-- Book 1

-- Book 2

- Authors

-- Author 1

-- Author 2

-- Author 3

The book template has a Page fieldtype which is linked to an author.

Right now I'm looping through all the authors - and if I can find a relation to a book based on the Page fieldtype - I show that author. I've got a feeling this might be accomplished by a selector. How can I display only authors who at least have one relation to books using a (single) selector?

[Soma]

I don't think it's a big problem looping all author, I think you won't have thousands anyway?

No it's not possible with a single selector, only the other way around.

An optimized query would be using direct SQL, and could be well made into a helper module.

Example: (removed)

Edit: just realized it's not exactly what you want, need to adapt a little

This will give you pages that have a reference to them via the defined field and are published:

$pagefield = "select_page";
$query = "SELECT p.id FROM pages p
            RIGHT JOIN field_$pagefield b ON p.id=b.data
            WHERE p.status < 2048";

$res = $db->query($query);
$ids = array();
while($row = $res->fetch_array()) $ids[] = $row['id'];

$pa = $pages->getByID($ids);
foreach($pa as $p) echo "<p>$p->title</p>";

[arjen]

There will be a few hunderd at minimum. I don't think it will reach into the thousands. I did some testing and your MySQL query works like a charm. Thanks for your answer soma!

[Soma]

I would do a relation the other way around, have authors reference their books. Isn't it?

Otherwise a trick could be used to when saving the book page to save a flag to the author selected, but as I said the "DB" design you have is wrong. I don't think there's books without an author.

Edit: It could be even flat hierachy, have the books as children of authors. Pretty easy, unless you need multiple authors per book.

[diogo]

I would do a relation the other way around, have authors reference their books. Isn't it?

In that case you would have to edit the author page every time you add a new book. For me arjen is thinking well

[arjen]

There are usually multiple authors related to one book, so from the content manager to my this is my preferred option. It would be handier otherwise

When testing your code works. I got some books with one author (and one book), with multiple authors with one book, multiple authors on one book, unique author on unique book and offcourse some authors with no books. It does seem to work fine. Or are you saying that this query shouldn't be used since it's not optimised? 

[Soma]

Well ok, it makes sense, but you seem to want to list authors and not books? So you can't use simple selectors and have to fall back to use SQL to query them for which have books and maybe how many.

No big problem, either way you can run into not being able to use PW selectors. Just was thinking out of the PW page "editing" context for a moment.

I once wrote a two way relation page field module that you would have selects on both author and books and when changing one side the other will update, this way you can run simple PW query on both sides. (not saying you should use this).

My example code isn't a problem and is the most simple way in that case.

[arjen]

I do want to list all authors on a /authors/ page, but only the one who are connected to books. So you say using this module I can reverse the relationshop and still be able to add authors to a book? That would solve my problem and the content manager will still be able to use it properly.

[Soma]

Yes, it was more of a proof of concept, but it's working fine. You just have to enter the template and fields of both ends in the module and it will take care when page is saved and map both ends.

[Soma]

Here's another idea to have a flag on the authors page you would keep track if there's any books of the author.

So a simple module that does the following on book page save

$authors = $pages->find("template=author");
foreach($authors as $author){
    $found = $pages->count("template=book, authors=$author");
    $author->of(false);
    if($found && $author->has_books == 0) {
        $author->has_books = 1;
        $author->save('has_books');
    } else if($author->has_books == 1){
        $author->has_books = 0;
        $author->save('has_books');
    }
}

After that you can simply use:

$authors_with_books = $pages->find("template=author, has_books=1);

Of course if you have  a lot of authors it may not as efficient. But it could be more optimized using a direct SQL then.

[arjen]

To avoid any confusion I think your last option is the best one. It produces at least very clean code. Will try to create a small module. Thanks again Soma. Couldn't have do this without you.