Join several tables need improvement

[nikiedev]

Hello! I have 5 tables:

articles_tags
articles_category
articles_status
articles
articles_tag_ids

and I need to collect data from first 4 of them. The working query looks like this:

SELECT
articles_tags.id AS t_id, 
articles_tags.name AS t_name,
articles_tags.url AS t_url, 
articles_category.id AS c_id, 
articles_category.name AS c_name, 
articles_category.url AS c_url,
articles_status.id AS s_id,
articles_status.name AS s_name,
articles.*
FROM articles_tags 
JOIN articles_category 
JOIN articles_status 
JOIN articles_tag_ids 
JOIN articles 
ON 
articles_tag_ids.tag_id = articles_tags.id 
AND 
articles_category.id = articles.category_id 
AND 
articles_tag_ids.article_id = articles.id
AND
articles.status = articles_status.id
WHERE
articles.status = 1

I try to make a query in my project with the join method from class but it doesn't work.
It would be great if the next functionality would be add to the class so, i could make the queries like this one:

public function getArticlesByTag($tag)
{
	return $this->db->select([
		'articles_tags.id AS t_id',
		'articles_tags.name AS t_name',
		'articles_tags.url AS t_url',
		'articles_category.id AS c_id',
		'articles_category.name AS c_name',
		'articles_category.url AS c_url',
		'articles_status.id AS s_id',
		'articles_status.name AS s_name',
		'articles.*'
	])->table('articles_tags')
		->join('articles_category')
		->join('articles_status')
		->join('articles_tag_ids')
		->join('articles')
		->on('articles_tag_ids.tag_id = articles_tags.id')
		->and('articles_category.id = articles.category_id')
		->and('articles_tag_ids.article_id = articles.id')
		->and('articles.status = articles_status.id')
		->where([
			'articles.status' => 1,
			'tag' => $tag
		]);
}

thank you.

[izniburak]

We could make a plan to develop some new features like that. Thank you for your advice. I'll try to improve the library.
Thanks.

[nikiedev]

Oh, i found a way to use the class methods, the code below works fine:

public function getArticlesByTag($tag, $status = 1)
{
	return $this->db->select([
		'articles.*',
		'articles_tags.id AS t_id',
		'articles_tags.name AS t_name',
		'articles_tags.url AS t_url',
		'articles_category.id AS c_id',
		'articles_category.parent_id AS c_parent_id',
		'articles_category.name AS c_name',
		'articles_category.url AS c_url',
		'articles_status.id AS s_id',
		'articles_status.name AS s_name'
	])->table(
		'articles'
	)->leftJoin(
		'articles_category',
		'articles.category_id',
		'=',
		'articles_category.id'
	)->leftJoin(
		'articles_tag_ids',
		'articles_tag_ids.article_id',
		'=',
		'articles.id'
	)->leftJoin(
		'articles_tags',
		'articles_tag_ids.tag_id',
		'=',
		'articles_tags.id'
	)->leftJoin(
		'articles_status',
		'articles.status',
		'=',
		'articles_status.id'
	)->where([
		'articles.status' => $status,
		'articles_tags.url' => $tag
	])->getAll();
}

If anyone has the similar problem, i leave the solution upper.

[izniburak]

Yes, as a solution; you can use a lot of join method as chain. But your suggestion is good as well. I will work on that. Thank again.