Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
multiple joins
  • Hi everyone, I'm trying to figure out how to solve this query. I hope you guys can help: To start of, here's the diagram of tables: http://dl.dropbox.com/u/1246624/members_topics_articles.jpg Here's my ORM definitions: MEMBERS has many TOPICS through MEMBERS_TOPICS ARTICLES has many TOPICS though ARTICLES_TOPICS TOPICS has many_many MEMBERS though MEMBERS_TOPICS TOPICS has many_many ARTICLES though ARTICLES_TOPICS Think of it as a mailing list where you have members that has chosen topics of articles in which topics are also assigned to particular topics. I couldn't figure out how to make a single query so I can return joined results and send an email to individual members with articles they only chose through the topics they've chosen. I hope to receive wisdom from mysql ninjas around. :D -Arnold
  • Thanks, Jelmer. As always, you've been helpful.
  • You'd create the query like this:
    $members = Model_Member::query()->related('topics')->related('topics.articles')->get()
    

    I do have one warning though: this has the potential to run out of memory very quickly: let's say you have n members, which each have m topics and those each have o articles. In this case you'd get n * m * o rows returned that need to be parsed into objects, this has the potential to become huge.
  • Jelmer Schreuder wrote on Friday 3rd of February 2012:
    You'd create the query like this:
    $members = Model_Member::query()->related('topics')->related('topics.articles')->get()
    

    I do have one warning though: this has the potential to run out of memory very quickly: let's say you have n members, which each have m topics and those each have o articles. In this case you'd get n * m * o rows returned that need to be parsed into objects, this has the potential to become huge.

    Thanks Jelmer. I think I'll just set a limit to the rows and just run the script every 5 minutes. Do you think this is the best database schema to run this kind of problem?
  • If you want fully personalized newsletters there's not really another way as I see it. The "lighter" solution would be to have some predefined topic combinations to which people can subscribe. That would be a lot lighter as you could generate the newsletter only a few times and fetch the subscribers lists separately, but would not be as personalized of course.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion