Streams Module: Get entries .where() using multiple relationship

Created 6 years ago by finnito

Hey all,

I have a stream of documents with various attributes, and a stream of custom wiki pages and I have set up a multiple relationship field for the documents so that I can relate each document to multiple wiki pages. What I want to do is to display the relevant documents on each wiki page and therefore I need to do a entries().where() and do a filter against the multiple relationship field. How can I best achieve this?

I see that when I dump the multiple relationship field that I get the entire entry of the related pages in there which is quite nice, but I'm having trouble with the where clause because it says Unknown column 'document_wiki_pages' when that is the correct column name for the query like this: {% set documents = entries("ucandance", "documents") .where("document_wiki_pages", "CONTAINS", pageID) .get() %}

Thanks for any help that can be given!

fryiee — 6 years ago Best Answer

@finnlesueurgmailcom depends which way you are filtering your multiple relationship. From what I can see, you are saying that documents have a multiple relationship FT (document_wiki_pages). If you just want the wiki pages for a document ID you already have, you can just do this: entries('ucandance', 'documents').find(documentId).documentWikiPages().get()

If you have the document itself, it's even easier: document.documentWikiPages().get()

If you want the other way, you'll need to define the inverse belongsToMany relationship on your wiki page model, inverting the entry_id and related_id like so:

    public function documents()
    {
        return $this->belongsToMany(
            \YourDocumentModel::class,
            'ucandance_documents_document_wiki_pages',
            'related_id',
            'entry_id'
        );
    }

You can then do the same as above, eg: entries('ucandance', 'document_wiki_pages').find(documentWikiPages).documents().first() or documentWikiPage.documents().first()

What i like to do here is, despite the Multiple FT creating a many-to-many relationship, it operates as a one to many. so I define the multiple relationship as a singular (such as document()) so I remember, and then create my own getter that always returns one like so:

public function getDocument()
{
    return $this->document()->first();
}

Let me know if anything of that doesn't make sense.

Link

fryiee — 6 years ago

@finnlesueurgmailcom EntryCriteria should accept has(), which uses the name of the relationship. e.g. entries('ucandance', 'documents').has('wiki_pages').get()

In your example, is the column actually called document_wiki_pages ? If it's a multiple relationship, it should be in a belongsTo (one-to-many), or a pivot table if many to many. I guess we'd need to see your rel / db setup to tell you the best way to go about what you're doing.

Link

ryanthompson — 6 years ago

You can also join and do all those Laravel-like query builder things.

Link

finnito — 6 years ago

Thanks @ryanthompson & @fryiee! I'll add some more details:

This is my stream of wiki pages:

mysql> describe ucandance_ucandance_wiki_pages;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sort_order       | int(11)          | YES  |     | NULL    |                |
| created_at       | datetime         | NO   |     | NULL    |                |
| created_by_id    | int(11)          | YES  |     | NULL    |                |
| updated_at       | datetime         | YES  |     | NULL    |                |
| updated_by_id    | int(11)          | YES  |     | NULL    |                |
| wiki_category_id | int(11)          | YES  |     | NULL    |                |
| wiki_content     | text             | YES  |     | NULL    |                |
| wiki_page_title  | varchar(255)     | YES  |     | NULL    |                |
| wiki_page_slug   | varchar(255)     | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

This is my table of documents:

mysql> describe ucandance_ucandance_documents;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sort_order           | int(11)          | YES  |     | NULL    |                |
| created_at           | datetime         | NO   |     | NULL    |                |
| created_by_id        | int(11)          | YES  |     | NULL    |                |
| updated_at           | datetime         | YES  |     | NULL    |                |
| updated_by_id        | int(11)          | YES  |     | NULL    |                |
| document_status      | varchar(255)     | YES  |     | Archive |                |
| document_category_id | int(11)          | YES  |     | NULL    |                |
| subcategory_id       | int(11)          | YES  |     | NULL    |                |
| document_year        | int(11)          | YES  |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

My multiple relationship is called document_wiki_pages so this is the table that appears to have been created to store this multiple relationship:

mysql> describe ucandance_ucandance_documents_document_wiki_pages;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| entry_id   | int(11)          | NO   | MUL | NULL    |                |
| related_id | int(11)          | NO   |     | NULL    |                |
| sort_order | int(11)          | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

My knowledge of one-to-many and many-to-one is very very minimal, hah.

Link

fryiee — 6 years ago Best Answer

If you have the document itself, it's even easier: document.documentWikiPages().get()

If you want the other way, you'll need to define the inverse belongsToMany relationship on your wiki page model, inverting the entry_id and related_id like so:

    public function documents()
    {
        return $this->belongsToMany(
            \YourDocumentModel::class,
            'ucandance_documents_document_wiki_pages',
            'related_id',
            'entry_id'
        );
    }

You can then do the same as above, eg: entries('ucandance', 'document_wiki_pages').find(documentWikiPages).documents().first() or documentWikiPage.documents().first()

public function getDocument()
{
    return $this->document()->first();
}

Let me know if anything of that doesn't make sense.

Link

finnito — 6 years ago

Okay I think that makes sense @fryiee! Where I do get stuck though is how to edit my wiki page model? I made the stream through the admin UI and am not sure where I should be finding the file I need to edit.

Link

finnito — 6 years ago

Where do I find the file to edit for my stream model @ryanthompson @fryiee? I've tried doing a php artisan streams:publish but can't locate the required file.

Thanks! 😄

Link

ryanthompson — 6 years ago

The streams module doesn't generate top level models like make:addon will. So you will need to bind to the core generated model in /storage/streams/default/models with a model you create on your own. Sometimes I put them in App\Streams\Namespace or in the theme since it's dedicated to the project.

Link

finnito — 6 years ago

Thanks @ryanthompson! Slowly getting the hang of it!

Link

jzachary — 5 years ago

I somewhat understand what you guys are talking about but I had a similar issue/question that may be a little different or exactly the same. Still new in Pyro, and I've only setup content in the Streams module using the UI.

I have a Stream of "people" and a stream of "groups." The People Stream has a multi relationship field related to the groups stream. So a person can be attached to multiple groups (or multiple groups attached to a single person, not sure the correct grammar on that one lol.)

https://imgur.com/a/FWayWXz here's a visual

using Entries plugin, I'm trying to list people that exist in a specific group and if there are multiple approaches and what the pros/cons would be on that.

Also, when doing multi relationships, for this example, is it better to put the relationship on the person to group, or group to person? In the UI and for the content management, it's easy to just on that persons entry chose the groups they need to belong to. Having it on the group would get cumbersome if the group grows in size, however those would be consolidated really nicely on that group entry for quick edit.

Can you have the best of both worlds? Meaning on the persons entry you can see the groups and when viewing the groups entry you would see the people attached? (in Admin panel)

Link