Querying.md

Querying

fmrest-spyke provides the following chainable querying methods.

.query

.query sets query conditions for a find request (with awareness of attribute mappings defined with attributes on your layout class).

String conditions are sent unchanged in the request, so you can use FileMaker find operators:

# Find records with names containing the word "Hutch"
Honeybee.query(name: "=Hutch")
# JSON -> {"query": [{"Name": "=Hutch"}]}

You can also pass date/datetime, or range Ruby objects as condition values, and they'll be converted into their matching FileMaker search strings:

Honeybee.query(age: 18..))
# JSON -> {"query": [{"Age": ">=18"}]}

Honeybee.query(date_of_birth: Date.today))
# JSON -> {"query": [{"DOB": "01/02/2021"}]}

Honeybee.query(date_of_birth: (Date.today-1..Date.today))
# JSON -> {"query": [{"DOB": "01/01/2021..01/02/2021"}]}

Passing multiple attributes to .query in a single conditions hash will group them in the same JSON object:

Honeybee.query(name: "=Hutch", age: 18..)
# JSON -> {"query": [{"Name": "=Hutch", "Age": ">=18"}]}

Calling .query multiple times (through method chaining) will by default merge the given conditions with the pre-existing ones (resulting in logical AND search of the given conditions):

Honeybee.query(name: "=Hutch").query(age: 20)
# JSON -> {"query": [{"Name": "=Hutch", "Age": 20}]}

NOTE: Prior to version 0.15.0, fmrest-ruby behaved differently in the above case, defaulting to logical OR addition of conditions with subsequent chained calls.

You can also pass multiple condition hashes to .query, resulting in a logical OR search:

Honeybee.query({ name: "=Hutch" }, { name: "=Maya" })
# JSON -> {"query": [{"Name": "Hutch"}, {"Name": "Maya"}]}

Alternatively you can prefix .or to chained call to .query to specify that you want conditions added as new condition hashes in the request (logical OR) instead of merged with pre-existing conditions:

Honeybee.query(name: "=Hutch").or.query(name: "=Maya")
# JSON -> {"query": [{"Name": "Hutch"}, {"Name": "Maya"}]}

# .or accepts conditions directly as a shorthand for .or.query
Honeybee.query(name: "=Hutch").or(name: "=Maya")
# JSON -> {"query": [{"Name": "Hutch"}, {"Name": "Maya"}]}

You can also query portal parameters using a nested hash (provided that you defined your portal in your layout class):

Honeybee.query(tasks: { urgency: "=Today" })
# JSON -> {"query": [{"Tasks::Urgency": "=Today"}]}

Passing strings instead of symbols for keys allows you to pass literal field names, useful if for some reason you haven't defined attributes or portals in your layout class:

Honeybee.query("Age" => 4, "Tasks::Urgency" => "=Today")
# JSON -> {"query": [{"Age": 4, "Tasks::Urgency": "=Today"}]}

Passing nil as a condition will search for an empty field:

Honeybee.query(name: nil)
# JSON -> {"query": [{"Name": "="}]}

Passing omit: true in a conditions hash will cause FileMaker to exclude results matching that conditions hash (see also .omit below for a shorthand):

Honeybee.query(name: "=Hutch", omit: true)
# JSON -> {"query": [{"Name": "=Hutch", "omit": "true"}]}

See .match below for a convenience exact-match companion for .query.

.omit

.omit works like .query but excludes matches by adding "omit": "true" to the resulting JSON:

Honeybee.omit(name: "Hutch")
# JSON -> {"query": [{"Name": "Hutch", "omit": "true"}]}

You can get the same effect by passing omit: true to .query:

Honeybee.query(name: "Hutch", omit: true)
# JSON -> {"query": [{"Name": "Hutch", "omit": "true"}]}

.and

.and takes the Cartesian product of existing and new parameters:

Honeybee.query({ name: "Hutch" }, { name: "Mitch" }).and({ age: 42 }, { age: 84 )
# JSON -> {"query":
#           [
#             {"Name": "Hutch", "Age": "42"},
#             {"Name": "Hutch", "Age": "84"},
#             {"Name": "Mitch", "Age": "42"},
#             {"Name": "Mitch", "Age": "84"}
#           ]
#         }

You can also chain .and with .match to perform the same operation with exact matches:

Honeybee.match({ name: "Hutch" }, { name: "Mitch" }).and.match({ age: 42 })
# JSON -> {"query": [{"Name": "==Hutch", "Age": "==42"}, {"Name": "==Mutch", "Age": "==42"}]}

NOTE: .and may only be used with .omit (or omit: true) when omitting the last clauses in the query:

Honeybee.query(age: 42).and(name: "Mitch").omit(name: "Hutch")
# JSON -> {"query": [{"Age": "42", "Name": "Mitch"}, {"Name": "Hutch", "omit": "true"}]}

Otherwise it will raise ArgumentError:

Honeybee.query(age: 42).omit(name: "Hutch").and(name: "Mitch")
# ArgumentError: Cannot use `and' with `omit'

.match

Similar to .query, but sets exact string match conditions by prefixing == to the given query values, and escaping any find operators through FmRest.e(). This is useful if you want to find for instance an exact email address:

Honeybee.match(email: "[email protected]")
# JSON -> {"query": [{"Email": "==hutch\@thehive.bee"}]}

You can also combine .or.match the same way you can .or.query to add new conditions through logical OR.

.limit

.limit sets the limit for get and find request:

Honeybee.limit(10)

NOTE: You can also set a default limit value for a model class, see other notes on querying.

You can also use .limit to set limits on portals:

Honeybee.limit(hives: 3, tasks: 2)

To remove the limit on a portal set it to nil:

Honeybee.limit(tasks: nil)

.offset

.offset sets the offset for get and find requests:

Honeybee.offset(10)

You can also use .offset to set offsets on portals:

Honeybee.offset(hives: 3, tasks: 2)

To remove the offset on a portal set it to nil:

Honeybee.offset(tasks: nil)

.sort

.sort (or .order) sets sorting options for get and find requests:

Honeybee.sort(:name, :age)
Honeybee.order(:name, :age) # alias method

You can set descending sort order by appending either ! or __desc to a sort attribute (defaults to ascending order):

Honeybee.sort(:name, :age!)
Honeybee.sort(:name, :age__desc)

NOTE: You can also set default sort values for a model class, see Other notes on querying.

.portal

.portal (aliased as .includes and .portals) sets which portals to fetch (if any) for get and find requests (this recognizes portals defined with has_portal):

Honeybee.portal(:hives)   # include just the :hives portal
Honeybee.includes(:hives) # alias method
Honeybee.portals(:hives, :tasks) # alias for pluralization fundamentalists

Chaining calls to .portal will add portals to the existing included list:

Honeybee.portal(:tasks).portal(:hives) # include both portals

If you want to disable portals for the scope call .portal(false):

Honeybee.portal(false) # disable portals for this scope

If you want to include all portals call .portal(true):

Honeybee.portal(true) # include all portals

For convenience you can also use .with_all_portals and .without_portals, which behave just as calling .portal(true) and portal(false) respectively.

NOTE: By default all portals are included.

.script

.script enables the execution of scripts during query requests.

Honeybee.script("My script").find_some # Fetch records and execute a script

See section on script execution below for more info.

Other notes on querying

You can chain all query methods together:

Honeybee.limit(10).offset(20).sort(:name, :age!).portal(:hives).query(name: "Hutch")

You can also set default values for limit and sort on the class:

class Honeybee < FmRest::Layout
  self.default_limit = 1000
  self.default_sort = [:name, :age!]
end

Calling any Enumerable method on the resulting scope object will trigger a server request, so you can treat the scope as a collection:

Honeybee.limit(10).sort(:name).each { |bee| ... }

If you want to explicitly run the request instead you can use .find_some on the scope object:

Honeybee.limit(10).sort(:name).find_some # => [<Honeybee...>, ...]

If you want just a single result you can use .first instead (this will force .limit(1)):

Honeybee.query(name: "Hutch").first # => <Honeybee...>

If you know the recordId of the record you should use .find(id) instead of .query(id: id).first (this would actually not work since recordId is not a queryable attribute).

Honeybee.find(89) # => <Honeybee...>

Note also that if you use .find(id) your .query() parameters (as well as limit, offset and sort parameters) will be discarded as they're not supported by the single record Data API endpoint.

A .first! method also exists, which raises an exception if no records matched your query. This is useful when using some form of unique identification other than recordId. E.g.

Honeybee.query(uuid: "BEE-2f37a290-f3ac-11ec-b939-0242ac120002").first!