#44 ✓resolved
Lucas Efe

Alias problem while joining one table two times

Reported by Lucas Efe | January 6th, 2009 @ 02:01 PM

Hello, the subject probably doesn't represent properly the title of my problem., I will try to explain it better.

I have the following model relationship: http://pastie.org/353960

As you can see, we have 5 models, but only two tables.

When I do this:

search ||= Movement.new_search(params[:search]) search.conditions.type = 'Outcome' search.conditions.origin_bucket_id_equals = @bucket.id search.conditions.origin.account_id = some_id search.include = [:target, :origin] search.all

I get the following error:

ActiveRecord::StatementInvalid (Mysql::Error: Not unique table/alias: 'buckets': SELECT movements.id AS t0_r0, movements.type AS t0_r1, movements.fecha AS t0_r2, movements.concepto AS t0_r3, movements.importe AS t0_r4, movements.bucket_origen_id AS t0_r5, movements.bucket_destino_id AS t0_r6, movements.created_at AS t0_r7, movements.updated_at AS t0_r8, movements.codigo_operacion AS t0_r9, movements.observaciones AS t0_r10, movements.pendiente AS t0_r11, movements.periodo_id AS t0_r12, buckets.id AS t1_r0, buckets.type AS t1_r1, buckets.nombre AS t1_r2, buckets.creador_id AS t1_r3, buckets.account_id AS t1_r4, buckets.created_at AS t1_r5, buckets.updated_at AS t1_r6, buckets.permalink AS t1_r7, origins_movements.id AS t2_r0, origins_movements.type AS t2_r1, origins_movements.nombre AS t2_r2, origins_movements.creador_id AS t2_r3, origins_movements.account_id AS t2_r4, origins_movements.created_at AS t2_r5, origins_movements.updated_at AS t2_r6, origins_movements.permalink AS t2_r7 FROM movements LEFT OUTER JOIN buckets ON buckets.id = movements.bucket_destino_id LEFT OUTER JOIN buckets origins_movements ON origins_movements.id = movements.bucket_origen_id LEFT OUTER JOIN buckets ON buckets.id = movements.bucket_origen_id WHERE (movements.type = 'Egreso' AND movements.bucket_origen_id = 3 AND buckets.account_id = 1 AND movements.fecha > '2008-11-01 02:00:00' AND movements.fecha < '2009-02-01 01:59:59') ORDER BY movements.fecha LIMIT 25):

If I remove the line (33 in the pastie)

search.conditions.origin.account_id = some_id

the error doesn't show up anymore.

I think, and this is only a theory/shot in the sky, that searchlogic is not using the table alias to build the conditions, or maybe I dont know how to specify this.

Anyway, thanks in advance.

(I am using the current version or searchlogic, with Rails 2.2.2)

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson January 6th, 2009 @ 02:07 PM

    • State changed from “new” to “open”

    Well search logic doesnt write SQL directly, except for the conditions. All that is does is prefer options for the ActiveRecord find method and then send them along their way. The problem isn't with searchlogic, it's with ActiveRecord, you are trying to do 2 joins with the same class, therefore ActiveRecord is using the same table alias.

    Do this for me, try to get this search to work with JUST active record, don't use searchlogic and see if you can get it to work. If so, then search logic should be able to duplicate that.

  • Lucas Efe

    Lucas Efe January 6th, 2009 @ 02:13 PM

    I will do that. Thank you.

    Love search logic, man. Really good work.

  • Ben Johnson

    Ben Johnson February 17th, 2009 @ 03:26 AM

    • State changed from “open” to “resolved”

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket