CodeIgniter在JOIN语句中的ActiveRecord字段名 [英] CodeIgniter ActiveRecord field names in JOIN statement

问题描述

我正在建立一个涉及JOIN的查询。这是我第一次使用活动记录完成数据库的东西，我遇到了一个麻烦。

I am building a query involving a JOIN. This is the first time I've done db stuff with Active Record and I've hit a bit of a snag.

我想加入一个表公司到用户表，所以我可以得到用户所在的公司的名称。我做了这样的成功如此：

I want to join a table called companies to the users table so I can get the name of the company etc the user is in. I've done this sort of successfully like so:

function get_profile_by_username($username)
{
    $this->db->join('companies', $this->table_name.'.company_id = companies.id');
    $this->db->where('LOWER(username)=', strtolower($username));
    $query = $this->db->get($this->table_name);
    if ($query->num_rows() == 1) return $query->row();
    return NULL;
}

/ code>，它们分别是 id 和 name c> name 。

However the issue being that the fields in companies, they are id and name are returned in that object as simply called name.

通常，当我写原始查询时，我会给表的别名，结果将是 u.company_id ， c.name 。所以我知道 name 与用户无关，但当然是公司的名称。虽然现在不是一个问题，但可能在将来， id 列显然不能在结果集中共存，所以一个覆盖！

Normally when I would write the raw query I would give aliases to the tables and the result would be something like u.company_id, c.name. So I'd know name had nothing to do with the user but of course is the name of the company. And although not an issue now but potentially in the future, the id column obviously can't coexist in the result set, so one gets overwritten!

我们如何能够区分某些表中的字段？或者有更好的方法来处理表连接和处理连接的查询数据集/对象？

How can we get this sort of differentiating between the fields that come from certain tables? Or is there a better way of going about table joins and working with joined query data sets/objects?

编辑：

如果我是一个原始查询，我会做：

If I was doing it as a raw query I'd do:

SELECT u.id, u.username, c.name
FROM users AS u
JOIN companies AS c
ON c.id = u.company_id
WHERE u.username = 'foobar';

这是伟大的，但如果我试图做这个活动记录我认为这是相当差的做法，如果

Which is great but if I tried to do that in active record I reckon that's pretty poor practice, if it works at all.

推荐答案

如果要从表中选择一些特定的列，请使用 db-> ; select（）。您可以给表别名，添加一些条件等。发送第二个参数 FALSE 以不转义特殊字符。

If you want to select some specific columns from table use db->select(). You can give alias to tables, add some conditions and etc. Send second parameter FALSE to not escape special characters.

$this->db->select('u.id, u.username, c.name', false);
$this->db->from('user as u');
$this->db->join('companies as c', 'u.company_id = c.id');
$this->db->where('LOWER(u.username)=', strtolower('foobar'));
$query = $this->db->get();

这篇关于CodeIgniter在JOIN语句中的ActiveRecord字段名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！