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