Deny | 9 Apr 15:49 2012
Picon

Raw SQL Query

Ada yang pernah eksekusi Raw SQL di Rails?
Kalau aku lihat kita harus menggunakan API (kelas-kelas) yang ada di
library Drivernya langsung ("pg" or "mysql" gem) dan tidak memperdulikan
ActiveRecord.

Nah kelihatanya ini bisa jadi masalah kalau tiba2 ingin ganti ke db engine
yg lain. Sementara ini aku pake pg, agak ragu kalau ganti ke mysql bisa
berjalan lancar atau tidak.
Sebenarnya problemnya cukup sederhana. Aku pingin mencari count()
berdasarkan Group tertentu.
self.connection.execute(sanitize_sql(["SELECT fb.country_id,
fb.date,fb.hour, count(fb) jumlah FROM featured_blocks fb WHERE fb.date = ?
AND fb.country_id = ? GROUP BY  fb.country_id, fb.date,fb.hour ", date,
country_id]))

Mungkin ada yang bisa memberi solusi lebih jitu?

--

-- 
*Deny Prasetyo, S.T.*

   - *Java(Script) Developer - 2ndStack Studio*
   - Java and Rails Developer and Trainer
      - Email :  jasoet87@...
      - https://github.com/jasoet
      - https://bitbucket.org/jasoet
      - Twitter:  <at> jasoet <http://www.twitter.com/jasoet>
      - Y! : jasoet87
      - GTalk : jasoet87

[Non-text portions of this message have been removed]
(Continue reading)

wuyouduan | 9 Apr 15:53 2012
Picon

Re: Raw SQL Query

Setau g itu ga akan bisa Deny

rails di buat agar penggunaan raw sql sedikit. Dengan tujuan agar minimal error ketika ganti engine db.

Solusi nya harus ganti raw sql semua nya dengan sesuai engine yg mau di pake..

ada yg punya solusi laen??
Sent from my BlackBerry®
powered by Sinyal Kuat INDOSAT

-----Original Message-----
From: Deny <jasoet87@...>
Sender: id-ruby@...
Date: Mon, 9 Apr 2012 20:49:02 
To: <id-ruby@...>
Reply-To: id-ruby@...
Subject: [id-ruby] Raw SQL Query

Ada yang pernah eksekusi Raw SQL di Rails?
Kalau aku lihat kita harus menggunakan API (kelas-kelas) yang ada di
library Drivernya langsung ("pg" or "mysql" gem) dan tidak memperdulikan
ActiveRecord.

Nah kelihatanya ini bisa jadi masalah kalau tiba2 ingin ganti ke db engine
yg lain. Sementara ini aku pake pg, agak ragu kalau ganti ke mysql bisa
berjalan lancar atau tidak.
Sebenarnya problemnya cukup sederhana. Aku pingin mencari count()
berdasarkan Group tertentu.
self.connection.execute(sanitize_sql(["SELECT fb.country_id,
fb.date,fb.hour, count(fb) jumlah FROM featured_blocks fb WHERE fb.date = ?
(Continue reading)

siagian alvian | 9 Apr 16:02 2012
Picon

Re: Raw SQL Query

Hi Friend

Wah kalo udah yakin mau pake sql ato pg dan tidak pindah ke engine yang lain seh tidak apa.2 tapi kalo tidak..
akan menyebabkan error... banyak sintaks query nya berbeda tiap engine... tapi rails dengan active
records sudah nge handle buat nge translate command query ke berbagi engine query nya... so saya yakin kan
anda untuk menggunakan active records (orm) sudah sangat powerfull kalo mau ganti engine query nya ..

Thanks

  

Elvin Alvian Siagian
Ruby on Rails Developper, 41Studio Inc

Tel: (+62) 852-224-93204| Fax: (+62) 22-665-1606www.41studio.com
   Facebook  Twitter  LinkedIn
Contact me:   elvin@...  elvin_alvian  henryss_10@...

Get a signature like this.  Click here.    

________________________________
 From: "wuyouduan@..." <wuyouduan@...>
To: id-ruby@... 
Sent: Monday, April 9, 2012 8:53 PM
Subject: Re: [id-ruby] Raw SQL Query

Setau g itu ga akan bisa Deny

rails di buat agar penggunaan raw sql sedikit. Dengan tujuan agar minimal error ketika ganti engine db.

(Continue reading)

Ahmy Yulrizka | 9 Apr 16:22 2012
Picon

Re: Raw SQL Query

from: http://guides.rubyonrails.org/active_record_querying.html

1.9.3p0 :042 > *FeaturedBlock*
 => FeaturedBlock(id: integer, country_id: integer, date: date, hour:
integer, created_at: datetime, updated_at: datetime)

1.9.3p0 :043 > *FeaturedBlock.create country_id:100, date: DateTime.now,
hour:200*
   (0.1ms)  begin transaction
SQL (0.7ms)  INSERT INTO "featured_blocks" ("country_id", "created_at",
"date", "hour", "updated_at") VALUES (?, ?, ?, ?, ?)  [["country_id", 100],
["created_at", Mon, 09 Apr 2012 14:16:16 UTC +00:00], ["date", Mon, 09 Apr
2012 21:16:16 +0700], ["hour", 200], ["updated_at", Mon, 09 Apr 2012
14:16:16 UTC +00:00]]
   (1.4ms)  commit transaction
  => #<FeaturedBlock id: 4, country_id: 100, date: "2012-04-09 21:16:16",
hour: 200, created_at: "2012-04-09 14:16:16", updated_at: "2012-04-09
14:16:16">

1.9.3p0 :044 > *FeaturedBlock.select("country_id, date, hour, count(*) as
jumlah").group("country_id, date, hour")*
  FeaturedBlock Load (0.6ms)  SELECT country_id, date, hour, count(*) as
jumlah FROM "featured_blocks" GROUP BY country_id, date, hour
 => [#<FeaturedBlock country_id: 100, date: "2012-04-09", hour: 200>]

1.9.3p0 :045 > *row =FeaturedBlock.select("country_id, date, hour, count(*)
as jumlah").group("country_id, date, hour").first*
  FeaturedBlock Load (0.6ms)  SELECT country_id, date, hour, count(*) as
jumlah FROM "featured_blocks" GROUP BY country_id, date, hour LIMIT 1
 => #<FeaturedBlock country_id: 100, date: "2012-04-09", hour: 200>
(Continue reading)

Deny | 9 Apr 16:27 2012
Picon

Re: Raw SQL Query

Iya, ternyata masalahnya ada di saya sendiri yang belum memaksimalkan
fungsi2 yang ada di active record,
Query yang saya butuhan ternyata bisa di solve dengan AR.

FeaturedBlock.select("country_id, date, hour, count(*) as
count").where("date = ? AND
country_id=?",date,country_id).group("country_id,date,hour").having("count(*)
>= ?",3)

Sekarang tinggal memikirkan AR sebagai SQL Query generator.

Problem Solved :D

Terima kasih pencerahanya

On Mon, Apr 9, 2012 at 9:22 PM, Ahmy Yulrizka <yulrizka@...> wrote:

> **
>
>
> from: http://guides.rubyonrails.org/active_record_querying.html
>
> 1.9.3p0 :042 > *FeaturedBlock*
> => FeaturedBlock(id: integer, country_id: integer, date: date, hour:
> integer, created_at: datetime, updated_at: datetime)
>
> 1.9.3p0 :043 > *FeaturedBlock.create country_id:100, date: DateTime.now,
> hour:200*
> (0.1ms) begin transaction
> SQL (0.7ms) INSERT INTO "featured_blocks" ("country_id", "created_at",
(Continue reading)


Gmane