Querying

Performing Database Queries

This ORM provides a suite of query functions under the db_adapter::query namespace. They extend the range of capability for the ORM further from just basic migration tracking to now querying data from the database. Below, the functions in this namespace that facilitate this are explained.

Note

To use these functions, include the following:

#include <strata/db_adapters.hpp>
#include "./models.hpp"

All results returned from the database are gonna be stored into a container inside each class that looks like this: std::vector<pqxx::row> records. This is the reason why most of these functions take an object of the class inside models.hpp.

db_adapter::query::fetch_all()

As the name implies, it fetches all the rows from a table specified. It requires the following arguments:

template <typename Model_T>
void fetch_all(Model_T& obj, std::string columns);

• Model_T& obj: This is an object of the class representing the model which represents the database table. This class is in the models.hpp file which was generated by the migrations.
• columns: This is to specify the columns you want returned. e.g "*" to return all columns, or maybe "username, password" to return only those two columns.

Usage Example:

db_adapter::query::fetch_all(user, "*");

Note

If the fetch fails, it will throw a std::runtime::error explaining why it failed.

db_adapter::query::get()

Now this function has two abilities:

• It can fetch from a database table and return a single row,
• It can determine if a previous database operation was conducted by checking the number of items in records and if there is content, it filters from there, otherwise, it fetches from the database.

template <typename Model_T, typename... Args>
void get(Model_T& obj, Args... args);

• Model_T& obj: Again, this is an object of the class, this must match the signature of the table you want to query. You can't pass a message object if you want to query the users table.
• Args... args: These are given in column-value pairs. e.g (..., "username", "triple_y", ...);. Each column must have a value after it.

Usage Example:

db_adapter::query::get(user, "username", "berna");

Note

• This function expects only one row returned, if more are returned, it will throw a std::runtime::error explaining what the error is and where it occured.
• This function also takes in an even number of arguments. if an odd number is passed, the program will raise an error during compile-time.
• This function only supports the equal sign operator during filtering, no other operators are used, e.g username='triple_y and not username>'triple_y' or any other operator.
• If you are fetching from the database(no other fetch operation has been called before this function is called), then if u are filtering a string, you have have to quote it e.g (..., "username", "'triple_y'",...).

db_adapter::query::filter()

This function has the same abilities as the get function above, but it has the added benefit of not being limited to one row. It can return an indefinite amount of rows.

template <typename Model_T>
void filter(Model_T& obj, std::string logical_op, filters& filters);

• Model_T& obj: Again, this is an object of the class representing the table you want to query.
• logical_op: This takes either "and" or "or", this is what goes between the conditions. e.g username = 'xxx' and/or email like %gmail%;
• filters: This is a vector that takes in the conditions for filtration. Each element is of the form:

{std::string column, OP::, std::variant<int, double, std::string> value}

OP is an enum which contains the operations which will be used for filtering. They are as follows:

enum OP{
  EQ=1,         //if column = value
  GT,           //if column > value
  LT,           //if column < value
  GTE,          //if column >= value
  LTE,          //if column <= value <int, double> supported for all of the above except OP::EQ which additionally supports a string
  LIKE,         //if column like value //case-sensitive
  ILIKE,        //if column ilike value //case-insensitive
  STARTSWITH,   //if column startswith the provided pattern //strings only
  ENDSWITH,     //if column startswith the provided pattern //works for strings only
  CONTAINS      //if column has a substring 'value' //works for strings only
};

Note

OP::LIKE and OP::ILIKE are only supported for database fetches, not filtering already existing rows in records.

Usage example:

filters filters = {
    {"email", OP::CONTAINS, "gmail"},
    {"username", OP::STARTSWITH, "b"}
};

db_adapter::query::filter(user, "or", filters);

Returned rows are in the records vector.

db_adapter::query::JoinBuilder

This is a struct with member functions that facilitate the building of a join query. To start, create an instance of the JoinBuilder as shown:

db_adapter::query::JoinBuilder JB {Model_T& obj}; //it takes an object of the class we want to join others to.

Note that this struct supports method chaining. After this, we can start building the query.

select(std::string columns)

This is self-explanatory, the select function should be called first, it takes a string containing the columns you want returned. e.g "*" or "username, email"

Join Functions

This include:

• inner_join()
• outer_join()
• full_join()
• left_join()
• right_join()

Each of them take a single std::string which represents the table name to join.

on(std::string logical_op, Args... args)

• logical_op: "and" or "or".

• Args... args: std::strings which are conditions to join the two tables on. e.g .on("and", "users.users_id = message.sender")

If one calls the on function before a join function has been called, a std::runtime_error will be thrown explaining that you need to call a join first.

str()

This method can be called if you want the actual query string. It returns a std::string.

execute()

This can be called after you are done with building the query and want to execute it. It returns a pqxx::result container with the returned rows.

Usage Example:

db_adapter::query::JoinBuilder JB {user};
pqxx::result result = JB.select("username, email")
                        .inner_join("message")
                        .on("and", "users.users_id = message.sender")
                        .execute();

Helper Functions

db_adapter::query::to_instances()

template <typename Model_T>
std::vector<Model_T> to_instances(Model_T& obj);

This function converts the rows inside records to instances of the class of type Model_T. This returns a function of the type std::vector<Model_T> containing all the instances.

db_adapter::query::to_values()

template <typename Model_T>
std::vector<decltype(std::declval<Model_T>().get_attr())> to_values(Model_T& obj);

This function converts the rows inside obj.records into tuples of the types of the class members. It returns a vector of tuples.