Sql query confusion

Hello champs,

Scenario is that i have a table called "employee" and a column for
manager_id inside it.. and another table called "leaves" and having a
column employee_id inside it.. I am copying here one record..

Employee table-
id employee_name manager_id created_at ..
1 hemant 1 whatever
2 hemant 1 whatever

Leave table-
id employee_id status created_at ..
1 1 pending whatever
2 2 granted whatever

Now i want to find out all the leave records of an employee whose
manager id is session id.. like i logged in using employee_id = 1, then
is should show me all leaves of employee whose manager id = 1..

Hope you people got me right, may be a one liner only .. :slight_smile:

Something like
Leave.find_by_employee_id(employee_id, :joins => :employee, :conditions
=> ["employees.manager_id=?", manager_id])

Hi Sharagoz,

This query is not working.. My model names are Leaves and Employee and
table names are leaves and employees. So i tried this query as:-
Leaves.find_by_employee_id (:employee_id, :joins => :employee,
:conditions => [ "employees.manager_id = ?",
session[:employee].manager_id ] )

But no results :cry:

Sharagoz -- wrote:

Hemant Bhargava wrote:

Hi Sharagoz,

This query is not working.. My model names are Leaves and Employee and
table names are leaves and employees. So i tried this query as:-
Leaves.find_by_employee_id (:employee_id, :joins => :employee,
:conditions => [ "employees.manager_id = ?",
session[:employee].manager_id ] )

But no results :cry:

You shouldn't be using both find_by_* and :conditions. Pick one or the
other.

Sharagoz -- wrote:

Something like
Leave.find_by_employee_id(employee_id, :joins => :employee, :conditions
=> ["employees.manager_id=?", manager_id])

Best,

Hi Sharagoz,

This query is not working.. My model names are Leaves and Employee and
table names are leaves and employees. So i tried this query as:-
Leaves.find_by_employee_id (:employee_id,

You're passing a symbol which I assume you didn't want to do (you may
also be more interested in find_all_by_employee_id)

Fred
:joins => :employee,

Marnen Laibow-Koser wrote:

Hemant Bhargava wrote:

Hi Sharagoz,

This query is not working.. My model names are Leaves and Employee and
table names are leaves and employees. So i tried this query as:-
Leaves.find_by_employee_id (:employee_id, :joins => :employee,
:conditions => [ "employees.manager_id = ?",
session[:employee].manager_id ] )

But no results :cry:

You shouldn't be using both find_by_* and :conditions. Pick one or the
other.

Also note that the first argument in your code is different from what
you were given.

Hemant, I've said this to you before and I'll say it again: go read some
basic Rails tutorials. You're posting a lot of beginner questions to
the list, and that's a waste of your time and ours. Please spend less
time posting and more time reading.

Marnen Laibow-Koser wrote:

Hemant Bhargava wrote:

Hi Sharagoz,

This query is not working.. My model names are Leaves and Employee and
table names are leaves and employees. So i tried this query as:-
Leaves.find_by_employee_id (:employee_id, :joins => :employee,
:conditions => [ "employees.manager_id = ?",
session[:employee].manager_id ] )

But no results :cry:

You shouldn't be using both find_by_* and :conditions. Pick one or the
other.

Ok fine.. Then i tried with using two queries:-
1) Leaves.find(:employee_id, :joins => :employee, :conditions => [
"employees.manager_id = ?", session[:employee].manager_id ] )

2) Leaves.find_by_employee_id (:employee_id, :joins => :employee)

Both of them are not working..

Marnen Laibow-Koser wrote:

Marnen Laibow-Koser wrote:

Hemant Bhargava wrote:

Hi Sharagoz,

This query is not working.. My model names are Leaves and Employee and
table names are leaves and employees. So i tried this query as:-
Leaves.find_by_employee_id (:employee_id, :joins => :employee,
:conditions => [ "employees.manager_id = ?",
session[:employee].manager_id ] )

But no results :cry:

You shouldn't be using both find_by_* and :conditions. Pick one or the
other.

Also note that the first argument in your code is different from what
you were given.

Hemant, I've said this to you before and I'll say it again: go read some
basic Rails tutorials. You're posting a lot of beginner questions to
the list, and that's a waste of your time and ours. Please spend less
time posting and more time reading.

Ohh .. Ok Sorry but i have already tried it using myself.. Used
googling.. Read joins .. But i came up with nothing thats why i posted
my ques here.. Anewayz, I'll take care of it in future as well..

Hemant Bhargava wrote:

Marnen Laibow-Koser wrote:

Hemant Bhargava wrote:

Hi Sharagoz,

This query is not working.. My model names are Leaves and Employee and
table names are leaves and employees. So i tried this query as:-
Leaves.find_by_employee_id (:employee_id, :joins => :employee,
:conditions => [ "employees.manager_id = ?",
session[:employee].manager_id ] )

But no results :cry:

You shouldn't be using both find_by_* and :conditions. Pick one or the
other.

Ok fine.. Then i tried with using two queries:-
1) Leaves.find(:employee_id, :joins => :employee, :conditions => [
"employees.manager_id = ?", session[:employee].manager_id ] )

2) Leaves.find_by_employee_id (:employee_id, :joins => :employee)

Both of them are not working..

Because your first argument in each case is *still* wrong. Look
carefully at the example that was given to you.

If you can't see the difference here, then you have no business
programming in Ruby without further study.

Part of the issue may also be that your model/table names are NOT following convention. The model should be singular, the table name plural underscored lower case:

Model Table

Leave leaves

Employee employees

Assume you also have these in your classes:

class Leave < ActiveRecord::Base

belongs_to :employee

end

class Employee < ActiveRecord::Base

has_many :leaves

belongs_to :manager, :class_name => “Employee”

has_many :subordinates, :class_name => “Employee”, :foreign_key => “manager_id”

end

so for Employee (Manager) with id 24:

manager = Employee.find(24)

leaves = manager.subordinates.collect(&:leaves) (You can also declare a relationship in the Employee model to mimic this if desired in a has_many :through)

If you made manager a separate model/table you’d adjust the relationships as appropriate.

Niels