Safe Haskell | None |
---|---|
Language | Haskell2010 |
Ordering, LIMIT
, OFFSET
and DISTINCT ON
Synopsis
- orderBy :: Order a -> Select a -> Select a
- data Order a
- asc :: SqlOrd b => (a -> Column b) -> Order a
- desc :: SqlOrd b => (a -> Column b) -> Order a
- ascNullsFirst :: SqlOrd b => (a -> Column b) -> Order a
- descNullsLast :: SqlOrd b => (a -> Column b) -> Order a
- limit :: Int -> Select a -> Select a
- offset :: Int -> Select a -> Select a
- distinctOn :: Default Unpackspec b b => (a -> b) -> Select a -> Select a
- distinctOnBy :: Default Unpackspec b b => (a -> b) -> Order a -> Select a -> Select a
- exact :: [Column b] -> (a -> Column b) -> Order a
- class PGOrd a
- type SqlOrd = PGOrd
Order by
orderBy :: Order a -> Select a -> Select a Source #
Order the rows of a Select
according to the Order
.
import Data.Monoid ((<>)) -- Order by the first field ascending. When first fields are equal -- order by second field descending. example ::Select
(Field
SqlInt4
,Field
SqlText
) ->Select
(Field
SqlInt4
,Field
SqlText
) example =orderBy
(asc
fst <>desc
snd)
An Order
a
represents a sort order and direction for the elements
of the type a
. Multiple Order
s can be composed with
mappend
or (<>)
from Data.Monoid. If two rows are
equal according to the first Order
in the mappend
, the second is
used, and so on.
Order direction
asc :: SqlOrd b => (a -> Column b) -> Order a Source #
Specify an ascending ordering by the given expression. (Any NULLs appear last)
desc :: SqlOrd b => (a -> Column b) -> Order a Source #
Specify an descending ordering by the given expression. (Any NULLs appear first)
ascNullsFirst :: SqlOrd b => (a -> Column b) -> Order a Source #
Specify an ascending ordering by the given expression. (Any NULLs appear first)
descNullsLast :: SqlOrd b => (a -> Column b) -> Order a Source #
Specify an descending ordering by the given expression. (Any NULLs appear last)
Limit and offset
limit :: Int -> Select a -> Select a Source #
Limit the results of the given query to the given maximum number of items.
WARNING: If you're planning on using limit/offset together please use
offset
before you use limit
, e.g.:
limit 10 (offset 50 yourQuery)
This is because Opaleye applies OFFSET and LIMIT to the query separately. The result of the query given above is the following, which will return 10 rows after skipping the first 50 (probably what you want).
SELECT * FROM (SELECT * FROM yourTable OFFSET 50) LIMIT 10
However, reversing the order of the limit/offset will result in the following, which will result in no rows being returned (probably not what you want).
SELECT * FROM (SELECT * FROM yourTable LIMIT 10) OFFSET 50
Distinct on
distinctOn :: Default Unpackspec b b => (a -> b) -> Select a -> Select a Source #
Keep a row from each set where the given function returns the same result. No
ordering is guaranteed. Mutliple fields may be distinguished by projecting out
tuples of Field_
s. Use distinctOnBy
to control how the rows
are chosen.
distinctOnBy :: Default Unpackspec b b => (a -> b) -> Order a -> Select a -> Select a Source #
Keep the row from each set where the given function returns the same result. The
row is chosen according to which comes first by the supplied ordering. However, no
output ordering is guaranteed. Mutliple fields may be distinguished by projecting
out tuples of Field_
s.
Exact ordering
exact :: [Column b] -> (a -> Column b) -> Order a Source #
Order the results of a given query exactly, as determined by the given list of input columns. Note that this list does not have to contain an entry for every result in your query: you may exactly order only a subset of results, if you wish. Rows that are not ordered according to the input list are returned after the ordered results, in the usual order the database would return them (e.g. sorted by primary key). Exactly-ordered results always come first in a result set. Entries in the input list that are not present in result of a query are ignored.
Other
Typeclass for Postgres types which support ordering operations.
Instances
PGOrd SqlCitext Source # | |
Defined in Opaleye.Order | |
PGOrd SqlUuid Source # | |
Defined in Opaleye.Order | |
PGOrd SqlTimestamptz Source # | |
Defined in Opaleye.Order | |
PGOrd SqlTimestamp Source # | |
Defined in Opaleye.Order | |
PGOrd SqlTime Source # | |
Defined in Opaleye.Order | |
PGOrd SqlText Source # | |
Defined in Opaleye.Order | |
PGOrd SqlNumeric Source # | |
Defined in Opaleye.Order | |
PGOrd SqlInt2 Source # | |
Defined in Opaleye.Order | |
PGOrd SqlInt4 Source # | |
Defined in Opaleye.Order | |
PGOrd SqlInt8 Source # | |
Defined in Opaleye.Order | |
PGOrd SqlFloat8 Source # | |
Defined in Opaleye.Order | |
PGOrd SqlFloat4 Source # | |
Defined in Opaleye.Order | |
PGOrd SqlDate Source # | |
Defined in Opaleye.Order | |
PGOrd SqlBool Source # | |
Defined in Opaleye.Order | |
PGOrd a => PGOrd (Nullable a) Source # | |
Defined in Opaleye.Order |