Scope 'near' does not work with pluck() #166

Open
santuxus opened this Issue Jan 25, 2012 · 10 comments

Comments

Projects
None yet
7 participants
@santuxus

Hello!

I needed to get array of only one column values from my locations table and it seems that pluck() always returns location.id, not depending what attribute is passed to the function.

Location.near('Lille')
=>
Location id: 4, area: "Lille", lat: 50.6292, lon: 3.05726, created_at: "2012-01-25 18:13:09",
Location id: 6, area: "Villeneuve-d'Ascq", lat: 50.6233, lon: 3.145, created_at: "2012-01-25 19:05:44"

Location.near('Lille').pluck(:id)
=> [4, 6]

Location.near('Lille').pluck(:created_at)
=> [4, 6]

Regards,
Santuxus

@alexreisner

This comment has been minimized.

Show comment Hide comment
@alexreisner

alexreisner Jan 26, 2012

Owner

This is going to be difficult to fix since pluck and near both modify the SELECT clause.

Owner

alexreisner commented Jan 26, 2012

This is going to be difficult to fix since pluck and near both modify the SELECT clause.

@rweng

This comment has been minimized.

Show comment Hide comment
@rweng

rweng Nov 23, 2012

can we detect that pluck is called and throw an exception? This could save some debugging time :(

rweng commented Nov 23, 2012

can we detect that pluck is called and throw an exception? This could save some debugging time :(

@alexreisner

This comment has been minimized.

Show comment Hide comment
@alexreisner

alexreisner Nov 26, 2012

Owner

That would be difficult as well. I just added a section to the README which explains why pluck doesn't work. Hopefully that will save others some time.

Owner

alexreisner commented Nov 26, 2012

That would be difficult as well. I just added a section to the README which explains why pluck doesn't work. Hopefully that will save others some time.

@rweng

This comment has been minimized.

Show comment Hide comment
@rweng

rweng Nov 26, 2012

Just an idea you've probably already considered: Is it possible to do the custom selects in a subselect?

rweng commented Nov 26, 2012

Just an idea you've probably already considered: Is it possible to do the custom selects in a subselect?

@alexreisner

This comment has been minimized.

Show comment Hide comment
@alexreisner

alexreisner Nov 28, 2012

Owner

I don't see how you could use a subquery (assuming that's what you mean by subselect) to fix this issue.

Owner

alexreisner commented Nov 28, 2012

I don't see how you could use a subquery (assuming that's what you mean by subselect) to fix this issue.

@danielpclark

This comment has been minimized.

Show comment Hide comment
@danielpclark

danielpclark Apr 9, 2015

You can use pluck if you option away order.

Address.near("New York, NY 10001", 300, {order: ""}).pluck(:addressable_id)

The SQL generated doesn't permit (some) things to be chained onto after the ORDER BY query is added. So just pass an options hash with an empty string for order and it removes that query part and your free to add more chainable commands.

I've tested pluck with many different column names and they all work.

Using Ruby 2.2.1, Rails 4.1.10, and geocoder 1.2.7

UPDATE

Looking at the SQL query there seems to be quite a difference. https://gist.github.com/danielpclark/7efbf51cf6d3e710564e

So it looks like pluck chops off the first half of the query... I don't believe that that is the desired result.

You can use pluck if you option away order.

Address.near("New York, NY 10001", 300, {order: ""}).pluck(:addressable_id)

The SQL generated doesn't permit (some) things to be chained onto after the ORDER BY query is added. So just pass an options hash with an empty string for order and it removes that query part and your free to add more chainable commands.

I've tested pluck with many different column names and they all work.

Using Ruby 2.2.1, Rails 4.1.10, and geocoder 1.2.7

UPDATE

Looking at the SQL query there seems to be quite a difference. https://gist.github.com/danielpclark/7efbf51cf6d3e710564e

So it looks like pluck chops off the first half of the query... I don't believe that that is the desired result.

@complistic-gaff

This comment has been minimized.

Show comment Hide comment
@complistic-gaff

complistic-gaff Jun 10, 2015

Thanks @danielpclark you just saved 2000+ ms off my load time!

I think the first half of the query was for generating the distance for ordering but seeing as you removed the order anyway it doesn’t seem to matter if the select gets eaten by pluck.

This is the code I'm using now an it seems to work:

address_ids = Address.near([lat, lng], 30, :units => :km, order: '').ids

Which gives me the SQL:

SELECT "addresses"."id" FROM "addresses"  WHERE (addresses.latitude BETWEEN -35.152174481775624 AND -34.61258151822438 AND addresses.longitude BETWEEN 138.30770837845907 AND 138.96548562154092 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((-34.882378 - addresses.latitude) * PI() / 180 / 2), 2) + COS(-34.882378 * PI() / 180) * COS(addresses.latitude * PI() / 180) * POWER(SIN((138.636597 - addresses.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 30)

Thanks @danielpclark you just saved 2000+ ms off my load time!

I think the first half of the query was for generating the distance for ordering but seeing as you removed the order anyway it doesn’t seem to matter if the select gets eaten by pluck.

This is the code I'm using now an it seems to work:

address_ids = Address.near([lat, lng], 30, :units => :km, order: '').ids

Which gives me the SQL:

SELECT "addresses"."id" FROM "addresses"  WHERE (addresses.latitude BETWEEN -35.152174481775624 AND -34.61258151822438 AND addresses.longitude BETWEEN 138.30770837845907 AND 138.96548562154092 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((-34.882378 - addresses.latitude) * PI() / 180 / 2), 2) + COS(-34.882378 * PI() / 180) * COS(addresses.latitude * PI() / 180) * POWER(SIN((138.636597 - addresses.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 30)
@danielpclark

This comment has been minimized.

Show comment Hide comment
@danielpclark

danielpclark Jun 11, 2015

Thanks for letting me know. Now I plan on using it. 👍

Thanks for letting me know. Now I plan on using it. 👍

@ajmiciano

This comment has been minimized.

Show comment Hide comment
@ajmiciano

ajmiciano Dec 4, 2015

Thanks @danielpclark!

Thanks @danielpclark!

@aydamacink aydamacink referenced this issue in sounddrop/sounddrop Aug 12, 2016

Merged

Drops within radius #80

@derekprior

This comment has been minimized.

Show comment Hide comment
@derekprior

derekprior Jan 17, 2018

Slightly different, way of doing the above in case you are composing things in way that doesn't let you easily change the call to near:

Address.near([lat, lng], 30).reorder('').pluck(:id)

Slightly different, way of doing the above in case you are composing things in way that doesn't let you easily change the call to near:

Address.near([lat, lng], 30).reorder('').pluck(:id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment