Kohana query builder

Question

i am building query in kohana framework which looks like this:

DB::select('users.email')->from('users', 'roles_users')->
where('users.id', '=', 'roles_users.user_id')->
and_where('roles_users.role_id', '=', '2');

it would output:

SELECT `users`.`email` FROM `users`, `roles_users` 
WHERE `users`.`id` = 'roles_users.user_id' 
AND `roles_users`.`role_id` = '2'

so my problem is that kohana puts ' ' for me on roles_users.user_id so mysql searches for text not for actual value of the field. is there any way to solve it?

score 3 · Accepted Answer · answered Nov 17 '11 at 23:13

3

Is there a reason you are not joining the other table? Like that:

DB::select('users.email')
->from('users')
->join('roles_users', 'LEFT')
->on('users.id', '=', 'roles_users.user_id')
->where('roles_users.role_id', '=', '2');

If you don't like that, try

DB::select('users.email')
->from('users', 'roles_users')
->where('users.id', '=', DB::expr('roles_users.user_id'))
->and_where('roles_users.role_id', '=', '2');

answered Nov 17 '11 at 23:13

Thorsten

5,634
6
35
33

Obviously first snippet is the solution. But I am wondering, whether it is possible to do using second snippet. there will not be any join, so how db will understand `where('users.id', '=', DB::expr('roles_users.user_id'))`? – Kowser Nov 18 '11 at 03:08
2

DB::expr('blablabla') means that 'blablabla' isn't escaped by query builder. – egis Nov 18 '11 at 06:28

score 2 · Answer 2 · answered Nov 18 '11 at 06:26

2

Have you tried with DB::expr()?

answered Nov 18 '11 at 06:26

egis

1,404
2
11
24

Kohana query builder

2 Answers2