Q: Rails complex database queries ?

Suppose you have 3 arrays of ids and you want to find only those records which have either of the ids contained in the given arrays of ids and you want to match a string.

like : @country_ids, @album_ids, @singers_ids, audio_name

 at = Audio.arel_table
 @audios=Audio.where(at[:album_id].in(@album_ids).or(at[:country_id].in(@country_ids)).or(at[:singer_id].in(@singers_ids)).or(at[:name].matches("%#{audio_name}%")))

Group Date Queries :

If we have date queries and we want to group data according to dates : Suppose we have a Count table in which their is count_reference which is given  value 1 when ever a new tuple/record is created now we can find out number of  tuples created per day.

@count_date_wise=Count.select("date(created_at) as ordered_date,sum(count_reference) as per_day_count").group("date(created_at)")

Further if we go more specific to one Attribute like person_id wise record created per day then it can be as:

@count_date_wise=Count.select("date(created_at) as ordered_date,sum(count_reference) as per_day_count").group("date(created_at)").where(:person_id => person_id)

Date formatting :  suppose we have a string like:

start_date="08/23/1989"

and we want to convert it into rails date then:

s_date=DateTime.strptime(start_date, '%m/%d/%Y')

now we can use this s_date in rails Date and DateTime queries.

Between Date queries : Suppose we want to find only those tuples/records which are created between two specific dates let say s_date and e_date are start_date and end_date respectively.

@record_between_dates=Count.where("created_at >= :start_date AND created_at <= :end_date AND person_id=:person_id", {:start_date => s_date, :end_date => e_date, :person_id => person_id})

.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: