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].
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.
