How can I relate pivot tables?

[ricardogj08]

Is there any example of how I can relate 2 tables through a pivot table?

[n0nag0n]

Just so I understand what you mean, you're talking about 3 tables right? And you would have something like:

users
id, name

comments
id, user_id, page_id

pages
id, url

And you want to go from the users table to the pages table?

[ricardogj08]

Just so I understand what you mean, you're talking about 3 tables right? And you would have something like:

users
id, name

comments
id, user_id, page_id

pages
id, url

And you want to go from the users table to the pages table?

yes, it is exactly the case

[ponasromas]

Better create mysql view for that, rather than joining in the code. That way it will be easier to maintain.

[n0nag0n]

You can absolutely do it with a view as well and it will accomplish what you are doing. Then you can actually create a record for that (only for reading).

I'm going off of pseudo-code here so it's untested but here is what you would do.

Setup 3 new records. UserRecord.php, CommentRecord.php, and PageRecord.php all pointing to their tables. In the UserRecord file you will set up a relation that I believe is ActiveRecord::HAS_MANY to the CommentRecord. Then in the CommentRecord file you will have 2 relations each with a ActiveRecord::BELONGS_TO one to the user table and one to the page table. In the PageRecord you would have a ActiveRecord::HAS_MANY to the CommentRecord. Then in your code you should be able to do something like this:

$UserRecord = new UserRecord();
$UserRecord->eq('company_id', 12345);
$Records = $UserRecord->findAll();

foreach $Records as $Record) {
    foreach($Record->comments as $Comment) { // or whatever you name your relation for comment
        echo $Comment->page->url; // or whatever you name your relation for page
    }
}

Now CAN you do this? Absolutely! You can totally make it work. SHOULD you do this? Honestly probably not. Let's say you have 500 users. So that's 1 query to pull back your users, and then under each foreach() loop you would run a query to pull out their comments (say 2 comments on average). Then for each comment, you would be running a query for each comment to pull the page url out. So in a few lines of code you have just run 1 user query + 500 comment queries + 1000 page queries for each comment. So you've just run 1501 queries against your database where a view would have solved that in one query.