SQL通过活动记录加入CodeIgniter [英] SQL join in CodeIgniter with Active Record

问题描述

我正在努力解决这个问题，但是我似乎盘旋了一下。我试图逐一列出用户主题，并在其底下引用属于该特定主题的引号。

I'm trying to wrap my head around this, but I seem to go in circles. I'm trying to list a users topics one by one, with the quotes belonging to that specific topic underneath. If that makes sense.

我有3张桌子，像这样：

I have 3 tables, like so:

[USERS] user_id用户名

[USERS] user_id username

[TOPICS] topic_id user_id topic_name

[TOPICS] topic_id user_id topic_name

[QUOTES] quote_id topic_id quote_name

[QUOTES] quote_id topic_id quote_name

我希望能够执行以下操作：

I want to be able to do something like this in my view:

用户名：Thomas

Username: Thomas

主题1：随便

Topic 1: Whatever

引号：一个引号，另一个引号和第三个引号，都属于主题1。

Quotes: One quote, another quote, and a third quote, all belonging to Topic 1.

主题2：来自Thomas的另一个主题

Topic 2: Another topic from Thomas

语录：是的，确实，谢谢，我喜欢Stack Overflow，这些语录属于主题2。

Quotes: Yes indeed, Okay thanks, I love Stack Overflow, These quotes belong to Topic 2.

但是我无法使它正常工作，我一直在尝试一切，包括诸如以下的奇怪内容：

But I can't get it to work, I've been trying everything, including weird stuff like:

public function get_quotes()
{

    $this->db->select('*');
    $this->db->from('topics');
    $this->db->join('quotes', 'topic_id = quote_id');

    $query = $this->db->get();

    if($query->num_rows() > 0)
    {
        foreach ($query->result() as $row) {
            $data[] = $row;
        }
    }
    return $data;
}

这很奇怪吗，我应该改用 where吗？

Is this strange, should I instead try using 'where' instead? Something like:

$this->db->where('user', $user_id);
$this->db->where('topic', $topic_id);
$this->db->where('quote', $quote_id);

我非常感谢我能获得的任何帮助，或者只是将手指指向正确的方向！

I really appreciate any help I can get, or just a finger pointed in the right direction!

推荐答案

我马上问什么不起作用？ ，其次我建议你运行分析器向您显示正在生成的 EXACT SQL ，以便您可以有效地评估 ACTIVE QUERY 使您失败的位置。

Right off the bat I would ask "What is not working?", secondly I would suggest you run the profiler to show you the EXACT SQL being generated, so that you can make a valid assessment of where the ACTIVE QUERY is failing you.

要使用分析器，请将其粘贴到您的控制器中：

To use the profiler, stick this into your controller:

$this->output->enable_profiler(TRUE);

这将导致所有数据库调用，所有POST var等的良好输出；

此处参考： http://codeigniter.com/user_guide/libraries/output.html

It will result in a nice output of all DB calls, all POST vars, etc;
Reference here: http://codeigniter.com/user_guide/libraries/output.html

更新

因此，要完全执行您想要的操作，您需要一个返回的查询以下列：

So to fully do what you want, you need a query that returns the following columns:

user_id, username, topic_id, topic_name, quote_id, quote_name

这里是您想要的活动查询（如果足够清楚，也可以使用方法链接）：

Here is the active query you want (you can also use method chaining if that is clear enough):

$this->db->select('u.user_id, u.username, t.topic_id, t.topic_name, q.quote_id, q.quote_name');
$this->db->from('users u');
$this->db->join('topics t', 't.user_id = u.user_id'); // this joins the user table to topics
$this->db->join('quotes q', 'q.topic_id = t.topic_id'); // this joins the quote table to the topics table
$query = $this->db->get();

您的结果集将类似于：

user_id     | username  | topic_id  | topic_name    | quote_id  | quote_name
1           |Thomas     |1          |Whatever       |1          |One quote, anot...
2           |Ryan       |4          |Another...     |6          |To be or not to...

一旦有了该结果集，只需循环遍历数据以输出它，然后检查是否有多个同一个人的引号（例如按user_id排序，如果第二个人名相同，则在第二个循环上进行测试，否则输出新的用户名）。

Once you have that result set, simply loop through the data to output it, and check to see if you have multiple quotes from the same person (say sort by user_id and do a test on the 2nd loop if its the same person, otherwise output the new users name).

这篇关于SQL通过活动记录加入CodeIgniter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！