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.
我正在构建一个涉及JOIN的查询。这是我第一次使用Active Record完成数据库操作，而且我遇到了一些麻烦。
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:
我想将一个名为companies的表加入到users表中，这样我就可以获得用户所在公司的名称等等。我已经成功完成了这样的事情：
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;
}
However the issue being that the fields in companies, they are id and name are returned in that object as simply called name.
然而，问题是公司中的字段，它们是id和name在该对象中返回，简称为name。
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!
通常，当我编写原始查询时，我会为表提供别名，结果将类似于u.company_id，c.name。所以我知道名字与用户无关，但当然是公司的名称。虽然现在不是问题，但可能在将来，id列显然不能在结果集*存，因此会被覆盖！
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?
我们如何区分来自某些表的字段？或者是否有更好的方法来进行表连接并使用连接的查询数据集/对象？
Edit:
编辑：
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.
哪个好，但如果我试图在活动记录中这样做，我认为这是非常糟糕的做法，如果它可以工作。
1 个解决方案
#1
36
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.
如果要从表中选择某些特定列，请使用db-> select（）。您可以为表提供别名，添加一些条件等。发送第二个参数FALSE以不转义特殊字符。
$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();
#1
36
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.
如果要从表中选择某些特定列，请使用db-> select（）。您可以为表提供别名，添加一些条件等。发送第二个参数FALSE以不转义特殊字符。
$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();