Skip to content

Latest commit

 

History

History
729 lines (525 loc) · 21.6 KB

README.md

File metadata and controls

729 lines (525 loc) · 21.6 KB
Raw

Babik

Build Status Maintainability Test Coverage

Gem

A Django queryset-like API for Ruby on Rails.

See Roadmap to check future enhancements.

See the QuerySet API if you know this library and want to see the documentation.

Contact me if you are interested in helping me developing it or make a PR with some feature or fix.

What's this?

This is a library to help you to make queries based on associations without having to worry about doing joins or writing the exact name of the related table as a prefix of the foreign field conditions.

Example: Blog platform in Rails

Suppose you are developing a blog platform with the following schema. Compare these two queries and check what is more easier to write:

Returning all users with last name equals to 'Fabia' that are from Rome:

User.joins(:zones).where('last_name': 'Fabia').where('geo_zones.name': 'Rome')
# vs.
User.objects.filter(last_name: 'Fabia', 'zone::name': 'Rome')

Returning all users with posts tagged with 'gallic' that are from Rome:

User.joins(:zones).joins(posts: :tags)
    .where('last_name': 'Fabia')
    .where('geo_zones.name': 'Rome')
    .where('tags.name': 'gallic')
# vs.
User.objects.filter(
  last_name: 'Fabia',
  'zone::name': 'Rome',
  'posts::tags::name': 'gallic'
)

The second alternative is done by using the powerful Babik querysets.

See Usage for more examples.

Install

Add to Gemfile:

gem install babik

or

gem install babik, git: 'git://github.com/diegojromerolopez/babik.git'

Requirements

Ruby Version >= 2.5

Include all inverse relationships in your models. It is required to compute the object selection from instance.

All your many-to-many relationships must have a through attribute. Per Rubocop guidelines, using has_and_belongs_to_many is discouraged.

Configuration

No configuration is needed, Babik automatically includes two methods for your models:

  • objects class method to make queries for a model.
  • objects instance method to make queries from an instance.

Database support

PostgreSQL, MySQL and Sqlite are fully supported.

MariaDB should work as well (happy to solve any reported issues).

Babik MSSQL support development is stalled by activerecord-sqlserver-adapter Rails 5.2 support issue. As soon as that issue is resolved, I will start working in providing full support to Babik to MSSQL.

No plan to offer Oracle support. Accepting contributors to port this library to Oracle.

Contributors can make PRs to support other RDBMS.

Documentation

See the QuerySet API documentation.

Main differences with Django QuerySet system

  • Django does not make any distinct against relationships, local fields or lookups when selecting by calling filter, exclude or get. Babik uses :: for foreign fields.
  • Django has a Q objects that allows the construction of complex queries. Babik allows passing an array to selection methods so there is no need of this artifact.
  • Django select_related method cache the objects in the returned object. We return a pair of objects and a hash with the associated objects. See doc here.

Known issues

Clone in each non-modifying method call

This library uses ruby_deep_clone to create a new QuerySet each time a non-modifying method is called:

julius = User.objects.filter(first_name: 'Julius')
julius_caesar = julius.filter(last_name: 'Caesar')

puts julius_caesar == julius
# Will print false

This library is somewhat unstable or not as stable as I would like.

Usage

For a complete reference and full examples of methods, see documentation.

See schema for information about this example's schema.

objects method

A new objects method will be injected in your ActiveRecord classes and instances.

Classes

When called from a class, it will return a QuerySet of objects of this class.

User.objects.filter(last_name: 'Fabia')
# Returning all users with last name equals to 'Fabia'

User.objects.filter(last_name: 'Fabia', 'zone::name': 'Rome')
# Returning all users with last name equals to 'Fabia' that are from Rome

Instances

When called from an instance, it will return the foreign related instances:

julius = User.objects.get(first_name: 'Julius')
julius.objects('posts').filter(stars__gte: 3)
# Will return the posts written by Julius with 3 or more stars

julius.objects('posts::tags').filter(name__in: ['war', 'battle', 'victory'])
# Will return the tags of posts written by Julius with the names 'war', 'battle' and 'victory'

Examples

Selection

See the main docs.

Basic selection is made by passing a hash to filter function:

User.objects.filter(first_name: 'Flavius', last_name: 'Josephus')
# SELECT users.* FROM users WHERE first_name = 'Flavius' AND last_name = 'Josephus'

To make an OR condition, pass an array of hashes:

User.objects.filter([{first_name: 'Flavius', last_name: 'Josephus'}, {last_name: 'Iosephus'}])
# SELECT users.*
# FROM users
# WHERE (first_name = 'Flavius' AND last_name = 'Josephus') OR last_name = 'Iosephus'

Selection by exclusion

You can make negative conditions easily by using exclude function:

User.objects.exclude(first_name: 'Flavius', last_name: 'Josephus')
# SELECT users.* FROM users WHERE NOT(first_name = 'Flavius' AND last_name = 'Josephus')

You can combine filter and exclude to create complex queries:

User.objects.filter([{first_name: 'Marcus'}, {first_name: 'Julius'}]).exclude(last_name: 'Servilia')
# SELECT users.*
# FROM users
# WHERE (first_name = 'Marcus' OR first_name = 'Julius') AND NOT(last_name = 'Servilia')

Selecting one object

# Returns an exception if more than one object matches the selection
User.objects.get(id: 258) 

# Returns the first object that matches the selection
User.objects.filter(id: 258).first

Selecting from an ActiveRecord

You can filter from an actual ActiveRecord object:

user = User.objects.get(id: 258)
user.objects('posts::tags').filter(name__in: %w[battle history]).order_by(name: :ASC)
# SELECT users.*
# FROM users
# LEFT JOIN posts posts_0 ON users.id = posts_0.author_id
# LEFT JOIN post_tag post_tags_0 ON posts_0.id = post_tags_0.post_id 
# WHERE post_tags_0.name IN ['battle', 'history']
# ORDER BY post_tags_0.name ASC
julius = User.objects.get(first_name: 'Julius', last_name: 'Caesar')

# Will return a QuerySet with only the Julius Caesar user (useful for aggregations) 
julius.objects

# Will return a QuerySet with all tags of posts of Julius Caesar
julius.objects('posts::tags') 

# Will return a QuerySet with the GeoZone of Julius Caesar
julius.objects('zone')
Lookups

See the main docs.

There are other operators than equal to, these are implemented by using lookups:

equal
User.objects.filter(first_name: 'Julius')
User.objects.filter(first_name__equal: 'Julius')
# SELECT users.*
# FROM users
# WHERE first_name = 'Julius'
exact/iexact
User.objects.filter(last_name__exact: nil)
# SELECT users.*
# FROM users
# WHERE last_name IS NULL 
User.objects.filter(last_name__exact: 'Postumia')
# SELECT users.*
# FROM users
# WHERE last_name LIKE 'Postumia'

i preceding a comparison operator means case-insensitive version:

User.objects.filter(last_name__iexact: 'Postumia')
# SELECT users.*
# FROM users
# WHERE last_name ILIKE 'Postumia'
contains/icontains
User.objects.filter(first_name__contains: 'iu')
# SELECT users.*
# FROM users
# WHERE last_name LIKE '%iu%' 
User.objects.filter(first_name__icontains: 'iu')
# SELECT users.*
# FROM users
# WHERE last_name ILIKE '%iu%'
endswith/iendswith
User.objects.filter(first_name__endswith: 'us')
# SELECT users.*
# FROM users
# WHERE last_name LIKE '%us' 
User.objects.filter(first_name__iendswith: 'us')
# SELECT users.*
# FROM users
# WHERE last_name ILIKE '%us'
startswith/istartswith
User.objects.filter(first_name__startswith: 'Mark')
# SELECT users.*
# FROM users
# WHERE first_name LIKE 'Mark%' 
User.objects.filter(first_name__istartswith: 'Mark')
# SELECT users.*
# FROM users
# WHERE first_name ILIKE 'Mark%'
in
User.objects.filter(first_name__in: ['Marcus', 'Julius', 'Crasus'])
# SELECT users.*
# FROM users
# WHERE first_name IN ('Marcus', 'Julius', 'Crasus')

There is also the possibility to use a subquery instead of a list of elements:

Post.objects.filter(id__in: @seneca_sr.objects(:posts).project(:id))
# SELECT posts.*
# FROM posts
# WHERE id IN (SELECT posts.id FROM posts WHERE author_id = 2)
Comparison operators: gt, gte, lt, lte
Posts.objects.filter(score__gt: 4)
# SELECT posts.*
# FROM posts
# WHERE score > 4
Posts.objects.filter(score__lt: 4)
# SELECT posts.*
# FROM posts
# WHERE score < 4
Posts.objects.filter(score__gte: 4)
# SELECT posts.*
# FROM posts
# WHERE score >= 4
Posts.objects.filter(score__lte: 4)
# SELECT posts.*
# FROM posts
# WHERE score <= 4
Other lookups

See more here.

Selection by foreign model field

The main feature of Babik is filtering by foreign keys.

Remember:

  • Your associations must have always an inverse (by making use of inverse_of).

  • Many-to-many relationships are only supported when based on has_many through. Reason.

Belongs to relationships
User.objects.filter('zone::name': 'Roman Empire')
# SELECT users.*
# FOR users
# LEFT JOIN geo_zones users_zone_0 ON users.zone_id = parent_zones_0.id
# WHERE  users_zone_0 = 'Roman Empire'

All depth levels are accepted:

User.objects.filter('zone::parent_zone::parent_zone::name': 'Roman Empire')
# SELECT users.*
# FOR users
# LEFT JOIN geo_zones users_zone_0 ON users.zone_id = parent_zones_0.id
# LEFT JOIN geo_zones parent_zones_0 ON users_zone_0.parent_id = parent_zones_0.id
# LEFT JOIN geo_zones parent_zones_1 ON parent_zones_0.parent_id = parent_zones_1.id
# WHERE  parent_zones_1 = 'Roman Empire'
Has many relationships
User.objects.distinct.filter('posts::tag::name': 'history')
# SELECT DISTINCT users.*
# FOR users
# LEFT JOIN posts posts_0 ON users.id = posts_0.author_id
# LEFT JOIN post_tag post_tags_0 ON posts_0.id = post_tags_0.post_id
# LEFT JOIN tags tags_0 ON post_tags_0.tag_id = tags_0.id
# WHERE  post_tag_tags_0 = 'history'

Note by using distinct we have avoided duplicated users (in case the same user has more than one post with tagged as 'history').

Projections

See the main docs.

Return an ActiveRecord Result with only the fields you are interested by using a projection:

p User.objects.filter('zone::name': 'Castilla').order_by('first_name').project('first_name', 'email')

# Query:
# SELECT users.first_name, users.email
# FROM users
# LEFT JOIN geo_zones users_zone_0 ON users.zone_id = parent_zones_0.id
# WHERE users_zone_0.name = 'Castilla'
# ORDER BY users.first_name ASC 

# Result:
# [
#   { first_name: 'Isabel I', email: '[email protected]' },
#   { first_name: 'Juan II', email: '[email protected]' },
#   { first_name: 'Juana I', email: '[email protected]' }
# ]

Select related

See the main docs.

select_related method allows fetching an object and its related ones at once.

User.filter(first_name: 'Julius').select_related(:zone)
# Will return in each iteration a list with two elements, the first one
# will be the User instance, and the other one a hash where the keys are
# each one of the association names and the value the associated object
Order

See the main docs.

Ordering by one field (ASC)

User.objects.order_by(:last_name)
# SELECT users.*
# FOR users
# ORDER BY users.last_name ASC

Ordering by one field (DESC)

User.objects.order_by(%i[last_name, DESC])
# SELECT users.*
# FOR users
# ORDER BY users.last_name DESC

Ordering by several fields

User.objects.order_by(%i[last_name, ASC], %i[first_name, ASC])
# SELECT users.*
# FOR users
# ORDER BY users.last_name ASC, users.first_name ASC

Ordering by foreign fields

User.objects
    .filter('zone::name': 'Roman Empire')
    .order_by(%i[zone::name, ASC], %i[created_at, DESC])
# SELECT users.*
# FOR users
# LEFT JOIN geo_zones users_zone_0 ON users.zone_id = parent_zones_0.id
# WHERE  users_zone_0 = 'Roman Empire'
# ORDER BY parent_zones_0.name ASC, users.created_at DESC

Inverting the order

User.objects
    .filter('zone::name': 'Roman Empire')
    .order_by(%i[zone::name, ASC], %i[created_at, DESC]).reverse
# SELECT users.*
# FOR users
# LEFT JOIN geo_zones users_zone_0 ON users.zone_id = parent_zones_0.id
# WHERE  users_zone_0 = 'Roman Empire'
# ORDER BY parent_zones_0.name DES, users.created_at ASC

Delete

See the main docs.

There is no standard DELETE from foreign field SQL statement, so for now the default implementation makes use of DELETE WHERE id IN SELECT subqueries.

Future implementations will use joins.

Delete by local field
User.objects.filter('first_name': 'Julius', 'last_name': 'Caesar').delete
# DELETE
# FROM users
# WHERE id IN ( 
#   SELECT users.*
#   FOR users
#   WHERE users.first_name = 'Julius' AND users.last_name = 'Caesar'
# )
Delete by foreign field
GeoZone.get('name': 'Roman Empire').objects('users').delete
User.objects.filter('zone::name': 'Roman Empire').delete
# Both statements are equal:
# DELETE
# FROM users
# WHERE id IN ( 
#   SELECT users.*
#   FOR users
#   LEFT JOIN geo_zones users_zone_0 ON users.zone_id = parent_zones_0.id
#   WHERE  users_zone_0 = 'Roman Empire'
# )

Update

See the main docs.

Similar to what happens in when running SQL-delete statements, there is no standard UPDATE from foreign field SQL statement, so for now the default implementation makes use of UPDATE SET ... WHERE id IN SELECT subqueries.

Future implementations will use joins.

Update by local field
User.objects.filter('first_name': 'Julius', 'last_name': 'Caesar').update(first_name: 'Iulius')
# UPDATE SET first_name = 'Iulius'
# FROM users
# WHERE id IN ( 
#   SELECT users.*
#   FOR users
#   WHERE users.first_name = 'Julius' AND users.last_name = 'Caesar'
# )
Update by foreign field
GeoZone.get(name: 'Roman Empire').objects('users').filter(last_name__isnull: true).update(last_name: 'Romanum')
User.objects.filter('zone::name': 'Roman Empire', last_name__isnull: true).update(last_name: 'Romanum')
# Both statements are equal:
# UPDATE SET last_name = 'Romanum'
# FROM users
# WHERE id IN ( 
#   SELECT users.*
#   FOR users
#   LEFT JOIN geo_zones users_zone_0 ON users.zone_id = parent_zones_0.id
#   WHERE  users_zone_0 = 'Roman Empire' AND users.last_name IS NULL
# )
Update field by using an actual value of the record
Post.objects.filter(stars__gte: 1, stars__lte: 4)
    .update(stars: Babik::QuerySet::Update::Increment.new('stars'))
# UPDATE SET stars = stars + 1
# FROM posts
# WHERE id IN ( 
#   SELECT posts.*
#   FOR posts
#   WHERE  posts.stars >= 1 AND posts.stars <= 4
# )

Documentation

See the documentation for more information about the API and the internals of this library.

Unimplemented API

Methods that return a QuerySet

Will be implemented

Will not be implemented

  • dates: project allow transformer functions that can be used to get dates in the desired format.
  • datetimes: project allow transformer functions that can be used to get datetimes in the desired format.
  • extra: better use the ActiveRecord API or for raw SQL use find_by_sql.
  • values: can be computed using project.
  • values_list: can be computed using project.
  • raw: use ActiveRecord find_by_sql. Babik is not for doing raw queries, is for having an additional query system to the ActiveRecord one.
  • using: to change the database a model is better to use something like this.

Under consideration

I am not sure it is a good idea to allow deferred loading or fields. I think is a poor solution for tables with too many fields. Should I have to take the trouble to implement this two methods?:

Methods that don't return a QuerySet

Will not be implemented

The aim of this library is to help make complex queries, not re-implementing the well-defined and working API of Rails. All of this methods have equivalents in Rails, but if you are interested, I'm accepting pull-requests.

Aggregation functions

Will be not implemented

  • expression: there are no Query Expressions in Babik, will be possible with the custom aggregations.
  • output_field: already possible passing a hash where the key is the output field.
  • filter: there are no Q objects in Babik.
  • **extra: no way to include extra keyword arguments in the aggregates for now.

Roadmap

Increase code quality

This project must follow Rubocop directives and pass Reek checks.

Make a babik-test project

Make a repository with the test schema to check the library is really working.

Deploy in rubygems

Deploy gem in rubygems.

Prefect

Object prefetching is not implemented yet.

Annotations

Annotations are not implemented yet.

Support other DBMS

Oracle is not supported at the moment because of they lack LIMIT clause in SELECT queries.

MSSQL support development is stalled by this issue. As soon as it is fixed, development will be re-started.

License

MIT