I have a column of a database table that is of type JSONB and I’m wanting to get some data from that column. For the most part the column is a flat list of key value pairs. Ex: However, the key I’m after contains an array of JSON data (or can be null/nil): Now, what I want I do
Tag: ruby-on-rails
How can I make these Active Record queries faster?
I’m using Active Record to send a few queries to a postgres database. I have a User model, a Business model and a joiner named UserBiz. The queries I mentioned go through the entire UserBiz collection and then filter out the businesses that match user provided categories and searches. These “work&…
Get users with no postings or no active postings
I have a rails schema and I have users table and postings table. I am trying to get all users that either have no postings or no active postings. Active being a column on postings that can be true or false. Is the following correct? The thing is, if the user has any postings that are active: true, I DO
Rails – deeply nested joins with ActiveRecord
I occasionally need to do a join in my app that goes across multiple tables, but haven’t quite figured out how to do this for more than 3 tables: Currently I’m ready to go: What I would like to do now is to join every consumer that belongs to an OrderListItem. However, when I try the following: I …
How to sanitize Arel SQL?
I have the following Arel SQL: I get SQL Injection warning when I run brakeman. I tried the following: However, I get the following error: How do I sanitize sql statement with Arel? Answer I am answering my own question. I am using Arel following the Github wiki for Ransack gem. I was doing something very sim…
How do I write a Ruby where clause with dates and array lookup?
I want to do something like the above. I first want to check if the model external id is in the array of appointment_ids, then make sure it is not soft deleted and then check if the record start time is between dates.. Any help much appreciated. Answer You don’t need to use a SQL string to create that q…
How to translate this jsonb SQL query to an Active Record query?
The following data is present in my Color ActiveRecord model: id colored_things 1 [{“thing” : “cup”, “color”: “red”}, {“thing” : “car”, &…
Creating a scope in Ruby on Rails, that returns an instance of an object only if all the associated instances fit a certain condition
I have a table called Carts with a has_many relationship to another table called Subcarts. I want to create a scope that returns all instances of the Cart table where all the associated Subcarts fits …
speed up the query which is taking 2 seconds on 1000000 on active records also on PostgreSQL Sql
I’m creating a dynamic query on the server side beside the parameter. However, my query is taking 2 seconds to fetch the records. I’m passing the query through active records let me share the query and Active record rails code SELECT (custom_attribute_values.attributable_id) FROM custom_attribute_…
How to count the sum of Invoices by last year using SQL in Rails?
I need to count all amount of invoices for last year. I do it using helper method but, as far as I know, there is better way using only one query to database with SQL function SUM(). My invoices table:…