Getting a column from a "through" table

[phobia]

I often have relation tables / "through" tables that has additional columns unique for the link.

Then if using ManyToMany on nodes to get categories for the node, I'd also, for example, need the template column to be part of the data returned.

Example

nodes: id, name categories: id, name node_category_link: node_id, category_id, template

I haven't found this possible with Jelly. Will it be part of Jelly in the future?

[ccazette]

I would also love this. Most ManyToMany relationships have additional columns. Jelly uses subqueries instead of JOINS at the moment for API consistency, and I don't quite see any workaround for now...

[leth]

Hmm, I'm working on something similar to what you've requested; see #103

It will allow HasMany and ManyToMany to be 'filtered' through methods on a model builder. You won't be able to specify the value in the template column dynamically though; but if you can think of a nice way of doing that I'll certainly consider it.

Here's some examples of it:

Tables:
    Member
        id

    Loan
        member_id
        book_id
        returned
        due

    Book
        id

class Model_Member extends Jelly_Model
{
    public static function initialize(Jelly_Meta $meta)
    {
        $meta->fields += array(
            ...

            'books_on_loan' => new Field_Filterable_ManyToMany(array(
                'foreign' => 'book',
                'through' => 'loan',
                'filter_through' => 'outstanding'
            )),
            'books_overdue' => new Field_Filterable_ManyToMany(array(
                'foreign' => 'book',
                'through' => 'loan',
                'filter_through' => 'overdue'
            )),
        );
    }
}

class Model_Builder_Loan extends Jelly_Builder
{
    public function outstanding()
    {
        return $this->where('returned', 'IS', NULL);
    }

    public function overdue()
    {
        return $this->where('due', '<', time())
                    ->outstanding();
    }
}

[banks]

Hmm. Leth that is an interesting solution. It occurs ot me that this demonstrates that any special relationship handling (i.e. through tables) could be implemented easily using custom relationship fields.

My opinion here is that this shouldn't be in the core just now as it is easily added with code specific to your needs.

I'll leave it to Jon to see what he thinks.

[leth]

banks: Indeed, but you'd need to create a new one for each different relationship.

On re-reading this thread I don't think the stuff i'm working on is very relevant to what the OP was asking for, sorry.

[banks]

Indeed, but you'd need to create a new one for each different relationship

No. Insofaras you could solve this nicely in the core, you could solve it nicely once using a custom field. You then use this same custom field in multiple models.

Basically, what I'm saying is that you should be able to implement through type relationships without touching the core - Jelly was specifically designed to allow this. If you implement it, it works well and loads of people use it, we may well consider adding your field(s) into the core distribution but I feel most of these are edge cases and will be hard to solve neatly with one general approach.

[leth]

Sorry I must have misunderstood what you said. I agree that the 'through' stuff needn't be in core unless it's popular, and yes Jelly is nice and extensible like this, its one of the reasons I stopped hacking sprig :)

I've sort of made this thread go a bit off-topic, sorry :P

[kgorton]

@ leth - thanks for the help you had given even though it was off topic. I am still looking at trying to access the specific properties of the pivot table through the query. i have tried adding the colum to the select statement, but this appears to be overwritten later in the Jelly_Builder Directly, so these values are still not accessable evn though they are used for filtering. Any Ideas?

[DanHulton]

Any news/progress on this? I find myself needing this as well. It can't be rare that people want to represent an "Order -> Item"-style relationship, storing the quantity of the item in the order on the "through" table.