finnlesueurgmailcom - 1 month ago

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!

Answer

fryiee - 1 month ago

@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.

fryiee - 1 month 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.

ryanthompson - 1 month ago

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

finnlesueurgmailcom - 1 month 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.

fryiee - 1 month ago

@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.

finnlesueurgmailcom - 1 month 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.

finnlesueurgmailcom - 3 weeks 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! :)

ryanthompson - 3 weeks 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.

finnlesueurgmailcom - 2 weeks ago

Thanks @ryanthompson! Slowly getting the hang of it!