Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
multiple joins
  • arnoldgamboaarnoldgamboa
    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
  • arnoldgamboaarnoldgamboa
    Thanks, Jelmer. As always, you've been helpful.
  • Jelmer SchreuderJelmer Schreuder
    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.
  • arnoldgamboaarnoldgamboa
    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?
  • Jelmer SchreuderJelmer Schreuder
    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.
Add a Comment

Howdy, Stranger!

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

Sign In Apply for Membership

Categories

In this Discussion