fbpx

Active Record Queries: Specifying Conditions on Associations

I ran into a ‘gotcha’ this week while setting up an active record query that already had an eager loaded association.

My goal was to only find objects that had an appointment time from the current time until the end of the day. In this case, appointments were the association and the objects were already set up to display in descending order.

Object.where(status: status)
      .includes(:appointments)
      .order('appointments.scheduled_datetime DESC')

The objects also needed to have a status of ‘scheduled’, which was already set to the variable status at the start of the controller action.

Using array conditions in my where statement, I started with the following:

Object.where(status: status)
      .includes(:appointments)
      .where('scheduled_datetime >= ? AND scheduled_datetime <= ?',             
        current_time, end_of_day)
      .order('appointments.scheduled_datetime DESC')

I had set the current_time and the end_of_day time to variables. The string making up the array condition looked good. Yet the above resulted in the following error:

PG::UndefinedTable: ERROR:  missing 
FROM-clause entry for table “appointments”

I realized that a references was needed for the conditions mentioned in the where statement. Using the example above doesn’t join the appointments table.

Object.where(status: status)
      .includes(:appointments)
      .where('scheduled_datetime >= ? AND scheduled_datetime <= ?',             
        current_time, end_of_day)
      .references(:appointments)
      .order('appointments.scheduled_datetime DESC')

Using references allows the query to know that the string in the where statement references appointments. It joins the appointments table to the query.

You may be reading this and wondering, “Couldn’t you also use joins here?”. You are right, you can use joins here, it would look something like this:

Object.where(status: status)
      .joins(:appointments)
      .where(
        'appointments.scheduled_datetime >= ? AND ' + 
          'appointments.scheduled_datetime <= ?',             
        current_time, end_of_day
       )

This looks cleaner, yet I also want to use order here. You cannot simply use order and joins when ordering on associations. You would need to use merge like this:

Object.where(status: status)
      .joins(:appointments)
      .where(
        'appointments.scheduled_datetime >= ? AND ' + 
          'appointments.scheduled_datetime <= ?',             
         current_time, end_of_day
      ).merge(Appointment.order(scheduled_datetime: :desc))

There you have it! If you are using includes in queries don’t forget the references!

We’re Hiring: Content Marketer

We are looking to hire a Content Marketer to tell our story to the world. 

The ideal Content Marketer will be a self-starter who is comfortable moving fast, and unafraid to try new content strategies and tactics to growth hack our outreach efforts. They can uncover and convey a story line through blogs in short and long format, will produce winning content for our Social Channels, and has a genuine enthusiasm for tech startups and community building.

TRIM is an agile Startup Studio. Most of our clients are in their first few years (or months!) of operation, so you’ll be challenged to execute in various cycles of business (validate, build, or grow). Our culture is fostered through instituting the importance of the 8 TLC’s (Exercise, Diet, Nature, Service, Relationships, Recreation, Relaxation / Stress Management, and Spiritual Health). We are also the creators of General Provision, the “Coolest Office in South Florida” as named by South Florida Business Journal, and home to Broward’s first coding bootcamp. You can find us in FATvillage, Fort Lauderdale, but our clients and their communities are all over the country.

 

Here are some things you might do:

  • Create branded content of all kinds, including social media, blogs, landing pages, and email campaigns primarily for T R I M and General Provision Coworking, but might cross-over to other Portfolio companies. 
  • Build creative for marketing campaigns in Facebook, Instagram, and the other channels.
  • Act as our in-house journalist and media team.
  • Promote the content you create far and wide, by identifying and engaging with key influencers and  partners, both online and offline. 
  • Establish and maintain a consistent brand, voice, and messaging strategy across all channels.
  • Monitor digital trends to stay up-to-date on how to develop our media capabilities and new ways to optimize our marketing decisions.

Requirements:

  • 2-3 years experience in creating digital content: Writing skills are a must, graphics, photo, and video production are an awesome bonus. 
  • Deep passion and knowledge of digital media management as it supports a company’s growth and business impact.
  • Excellent writing and grammar skills and the ability to adjust the voice and tone of your content to the appropriate audience
  • Attention to detail for efficient and effective communication, externally and internally
  • An entrepreneurial attitude where no job is too small and no task too daunting.
  • We are a startup that builds startups; high energy and a positive outlook fit our culture best.

Upload Resume