Nice workaround Tim!
El 10-06-2015 a las 05:08, Tim Griffin escribió:
> What could be better than googling for help and finding your own notes?
>
> I'm adding another scenario that's just shown itself as a related
> problem with an empty /scopes/ in apply_scopes in Rails 4.0.x:
>
> I have a User model that /has_many/ Filings.
>
> In an index method, I had hoped to do this:
>
> scopes = {
>
> search: [params[:search], :description],
> order_by: parse_sort_param(:date_submitted, :description)
> }
> @filings = current_user.filings.apply_scopes(scopes)
>
> but, this doesn't work when /scopes /is empty: The query simply returns
> /*all*/ the filings, not just those specific to a user.
>
> I guess, as in my original posting below, this is effectively calling a
> ./where/ clause before calling the ./apply_scopes/, and that DOESN'T
> WORK. /apply_scopes *must* be called before a .where clause. /
>
> Instead, I have to do this:
>
> @filings = Filing.apply_scopes(scopes).where( user_id: current_user.id )
>
> Not quite as nice, but it works correctly when /scopes/ is empty.
>
> Tim
>
>
>
>
>
> On Tuesday, April 21, 2015 at 9:46:59 PM UTC-4, Tim Griffin wrote:
>
> This is not a problem looking for a solution, but just an
> observation that might save someone else some head-scratching.
>
> This issue has been reported as Issue 152
> <https://github.com/Hobo/hobo/issues/152>.
>
> *It seems that it is very important to call ./apply_scopes/ BEFORE
> calling a ./where/ clause, especially if the scope is effectively
> empty. *
>
> Calling ./apply_scopes/ with an empty scope AFTER a ./where/ clause
> causes the .where clause to be ignored!
>
> An example:
>
> In an #index function, I watch for search and sorting scopes:
>
> def index
>
> scopes = {
>
> :search => [params[:search], :user_name, :search_text],
> :order_by => parse_sort_param(:id, :date_searched, :user_name,
> :matches, :search_text)
> }
> @searches = Search
> .where('date_searched >= ? AND date_searched <= ?',
> params[:start_date], params[:end_date] )
> .apply_scopes(scopes).order('date_searched DESC')
> .paginate(:per_page => 200, :page => params[:page])
>
>
> I've found, though, that if my request does *not* include a search
> and an order_by parameter, the call to /apply_scopes /causes the
> .where clause to be ignored!
>
> For example, this is the request to filter by date:
>
> GET
> "/admin/searches?start_date=04%2F01%2F2015&end_date=04%2F30%2F2015"
>
> And this is the SQL (for SQL Server) that's generated:
>
> [INFO ] Parameters: {"start_date"=>"04/01/2015",
> "end_date"=>"04/30/2015"}
> [DEBUG] Search Load (98.4ms) EXEC sp_executesql N'SELECT TOP
> (200) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY
> date_searched DESC) AS [__rn], [searches].* FROM [searches] ) AS
> [__rnt] WHERE [__rnt].[__rn] > (0) ORDER BY [__rnt].[__rn] ASC'
>
> How bizarre!
>
> But, if it /*does*/ include a search parameter to be used by
> apply_scopes:
>
> [INFO ] GET
>
> "/admin/searches?start_date=04%2F01%2F2015&end_date=04%2F30%2F2015&search=Tim"
>
> I get this SQL:
>
> [DEBUG] Search Load (8.8ms) EXEC sp_executesql N'SELECT TOP (200)
> [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY date_searched
> DESC) AS [__rn], [searches].* FROM [searches] WHERE (date_searched
> >= N''04/01/2015'' AND date_searched <= N''04/30/2015'') AND
> (((searches.user_name LIKE N''%Tim%'') OR (searches.search_text LIKE
> N''%Tim%''))) ) AS [__rnt] WHERE [__rnt].[__rn] > (0) ORDER BY
> [__rnt].[__rn] ASC'
>
>
> SOLUTION:
>
> If I *reverse* the order of the .where and the .apply_scopes, it
> works as expected, which is completely unintuitive!
>
> @searches = Search
> .apply_scopes(scopes)
> .order('date_searched DESC')
> .where('date_searched >= ? AND date_searched <= ?',
> params[:start_date], params[:end_date] )
>
> [DEBUG] Search Load (6.5ms) EXEC sp_executesql N'SELECT TOP (200)
> [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY date_searched
> DESC) AS [__rn], [searches].* FROM [searches] WHERE (date_searched
> >= N''04/01/2015'' AND date_searched <= N''04/30/2015'') ) AS
> [__rnt] WHERE [__rnt].[__rn] > (0) ORDER BY [__rnt].[__rn] ASC'
>
> In thinking about crafting this query, it made more sense to me to
> reduce the overall set first (with the .where clause), and then
> apply any search (or sorting) within that.
>
> I had no idea that the order of .where and .apply_scopes was so
> important!
>
>
> Tim
>
> --
> You received this message because you are subscribed to the Google
> Groups "Hobo Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/hobousers.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups "Hobo
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/hobousers.
For more options, visit https://groups.google.com/d/optout.
