+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: What is the best practice for querying a DB with 2 or 3 tables (/showthread.php?tid=91077)
What is the best practice for querying a DB with 2 or 3 tables - spreaderman - 06-12-2024
I have 3 tables:
BlogTable
BlogTagTable - Record one row for each tag in a blog post. Eg. if 3 tags, will be three lines in the db with reference to the tagId and BlogId.
TagTable - master list of possible tags with names.
When I querybuild to get all blog posts I could make one large join. This will produce a large result though. For instance, if a blog post has 10 tags, I would get 10 lines back. I would assume that all query code would go in the BlogModel even if querying tags, right?
Alternatively, I could query the Blog table first, get all blog posts and then for each blog post, query the tags used. Would both query code go in BlogModel or just the blog query in BlogModel and tag query in TagModel?
I also thought of method chaining like getBlog()->withTags()->paginate() etc; I would pass the blog object to tags and then add an array of used tags in the blog post. What are your thoughts?
I appreciate all of your comments or suggestions!
RE: What is the best practice for querying a DB with 2 or 3 tables - InsiteFX - 06-12-2024
Good read.
Multiple Joins or Simple Readable Database Calls
RE: What is the best practice for querying a DB with 2 or 3 tables - paulkd - 06-13-2024
(06-12-2024, 07:17 PM)spreaderman Wrote: I have 3 tables:
BlogTable
BlogTagTable - Record one row for each tag in a blog post. Eg. if 3 tags, will be three lines in the db with reference to the tagId and BlogId.
TagTable - master list of possible tags with names.
When I querybuild to get all blog posts I could make one large join. This will produce a large result though. For instance, if a blog post has 10 tags, I would get 10 lines back. I would assume that all query code would go in the BlogModel even if querying tags, right?
Alternatively, I could query the Blog table first, get all blog posts and then for each blog post, query the tags used. Would both query code go in BlogModel or just the blog query in BlogModel and tag query in TagModel?
I also thought of method chaining like getBlog()->withTags()->paginate() etc; I would pass the blog object to tags and then add an array of used tags in the blog post. What are your thoughts?
I appreciate all of your comments or suggestions!
Hi,
I'm going to assume that you don't have 1000s of posts and I'm not a SQL expert, but here's how I would bring back records searching on tags.
This function would be in a Blogs Model file. It's rare that I use builder so I wasn't able to work out a "builder" version. HTH.
PHP Code:
public function getPosts(array $tags = []): array
{
if ($tags === []) {
return [];
}
$sql = "
SELECT b.*
FROM BlogTable b
WHERE b.id IN (
SELECT DISTINCT t.id
FROM BlogTags t
INNER JOIN BlogTagTable btt ON t.id = btt.tag_id
WHERE t.tag IN :tags:
)
ORDER BY b.date_added DESC
";
$query = $this->db->query($sql, ['tags' => $tags]);
return $query->getResultArray();
}
RE: What is the best practice for querying a DB with 2 or 3 tables - Rizzk - 07-31-2024
A large join query retrieves all data at once but may produce a lot of results, and can be handled in the BlogModel. Alternatively, you can first fetch blog posts and then query tags separately, splitting the logic between BlogModel and TagModel. Method chaining, like getBlog()->withTags()->paginate(), offers a clean approach by keeping related logic organized and accessible within the BlogModel, making it a flexible and efficient choice.