CI4 Models and Table Joins

I'm using a cross reference table to have a many to many relationship in a database.  With the CI4 model, the table name and table columns are specified.  Is there a CI-standard way to combine three tables?

i.e. a better way of doing this?
select * from products JOIN xref_prod_cat ON products.id = xref_prod_cat.product JOIN categories ON xref_prod_cat.category = categories.id WHERE categories.name = "some search term"

@rockinmusicgv ,

You could make the current query (listed above) into a database VIEW (that resides on the database). Then in your CI code all you have to do is call the view with the specific criteria.

Thank you for your reply.

This is a database with tens of millions of rows. I'm not used to datasets anywhere near this size, is using a view practical for such a large dataset?

Over the past few hours, I've begun to wonder if it is more worthwhile to sacrifice space to increase speed.

Since a lot of the data is slow moving (a store building, for example, is unlikely to move), I wonder if it would be better to have two databases. Instead of having convoluted joins and recomputing the view each query, I'd have most of the data "precached" in CouchDB documents. I'd pull some basic data from the SQL Server, then find the correct "cache" document in CouchDB.

@rockinmusicgv ,

Have you analyzed your queries to verify that they are efficient? Do you have proper indexes. Maybe a database analysis might do you some good.

I'm currently using SQLite3 for the database. I'm not sure how that might affect performance - though since it's currently on a single core VPS maybe I shouldn't be worrying about this kinda thing...

@rockinmusicgv ,

I thought you where using MySQL. This might be helpful to you https://codeigniter.com/user_guide/datab...eries.html. Also, in most cases if you are dealing with performance issues it is usually on the database side. That is why I asked you about doing a database analysis. This will help you determine whether or not your queries are efficient. For example...using EXPLAIN will definitely tell you if your query is efficient.