Look ahead and N+1 query problem

[takeit]

Hi, I'm a beginner to Rust and I try to build a simple GraphQL API using Juniper, Rocket and Diesel (postgres).

I can't quite figure out how this look ahead thing works and how to use it to solve the N+1 query problem as described here #234, here #136 and here #167. I just can't figure it out. The below code works fine but it makes a lot of queries.

I have added the comments in the code with questions.

my schema.rs:

table! {
    swp_article (id) {
        id -> Int4,
        slug -> Varchar,
        title -> Varchar,
        body -> Text,
        lead -> Text,
        published_at -> Timestamp,
        route_id -> Int4,
    }
}

table! {
    swp_route (id) {
        id -> Int4,
        name -> Varchar,
    }
}

table! {
    swp_article_media (id) {
        id -> Int4,
        article_id -> Int4,
        image_id -> Int4,
        key -> Varchar,
        body -> Text,
        description -> Text,
        located -> Varchar,
        by_line -> Varchar,
        mimetype -> Varchar,
        usage_terms -> Text,
    }
}

table! {
    swp_image (id) {
        id -> Int4,
        asset_id -> Varchar,
        file_extension -> Varchar,
    }
}

joinable!(swp_article -> swp_route (route_id));
joinable!(swp_article_media -> swp_image (image_id));

allow_tables_to_appear_in_same_query!(
    swp_article,
    swp_route,
    swp_image,
    swp_article_media
);

my models.rs:

use chrono::prelude::*;
use crate::schema::{swp_article, swp_route, swp_article_media, swp_image};
use crate::db::DbConn;
use diesel::deserialize::Queryable;

#[derive(Identifiable, Queryable, Debug, Clone)]
#[table_name = "swp_article"]
pub struct Article {
    pub id: i32,
    pub slug: String,
    pub title: String,
    pub body: String,
    pub lead: String,
    pub published_at: NaiveDateTime,
    pub route_id: i32,
}

#[derive(Identifiable, Queryable, Debug, Clone)]
#[derive(GraphQLObject)]
#[table_name = "swp_route"]
pub struct Route {
    pub id: i32,
    pub name: String,
}

#[derive(Identifiable, Queryable, Associations, Debug, Clone)]
#[belongs_to(Article)]
#[table_name = "swp_article_media"]
pub struct ArticleMedia {
    pub id: i32,
    pub article_id: i32,
    pub image_id: i32,
    pub key: String,
    pub body: String,
    pub description: String,
    pub located: String,
    pub by_line: String,
    pub mimetype: String,
    pub usage_terms: String
}

#[derive(Identifiable, Queryable, Debug, Clone)]
#[derive(GraphQLObject)]
#[table_name = "swp_image"]
pub struct Image {
    pub id: i32,
    pub asset_id: String,
    pub file_extension: String
}

my graphql.rs

use juniper::{Context as JuniperContext, FieldResult, FieldError};
use super::models2::Article;
use super::models2::ArticleMedia;
use super::models2::Image;
use super::models2::Route;
use crate::db::DbConn;
use diesel::prelude::*;
use diesel::debug_query;
use chrono::prelude::*;
use diesel::sql_query;
use crate::juniper::LookAheadMethods;

pub struct Context {
    pub connection: DbConn
}

impl JuniperContext for Context {}

pub struct Query;

#[juniper::object(
    Context = Context,
)]
impl Article {
    fn id(&self) -> i32 {
        self.id
    }

    fn title(&self) -> &str {
        self.title.as_str()
    }

    fn slug(&self) -> &str {
        self.slug.as_str()
    }

    fn body(&self) -> &str {
        self.body.as_str()
    }

    fn lead(&self) -> &str {
        self.lead.as_str()
    }

    fn published_at(&self) -> NaiveDateTime {
        self.published_at
    }

    fn route(&self, context: &Context) -> Route {
        use crate::schema::swp_route::dsl;

       // how to get rid off N +1 query problem?

        let route = dsl::swp_route
            .find(self.route_id).get_result::<Route>(&*context.connection)
            .expect("Error loading posts");
        route
    }

    fn media(&self, context: &Context) -> FieldResult<Vec<ArticleMedia>>  {
        use crate::schema::*;

        let query = ArticleMedia::belonging_to(self);

        // let debug = debug_query::<diesel::pg::Pg, _>(&query);
        // println!("{:?}", debug);


        // let look_ahead = executor.look_ahead();
        // let res = look_ahead.arguments();
        // println!("{:?}", look_ahead);
       
        // how to get rid off N +1 query problem?

        let article_media = query
            .load::<ArticleMedia>(&*context.connection)
            .expect("Error loading media");

        Ok(article_media)
    }
}

#[juniper::object(
    Context = Context,
)]
impl ArticleMedia {
    fn id(&self) -> i32 {
        self.id
    }

    fn description(&self) -> &str {
        self.description.as_str()
    }

    fn located(&self) -> &str {
        self.located.as_str()
    }

    fn usage_terms(&self) -> &str {
        self.usage_terms.as_str()
    }

    fn body(&self) -> &str {
        self.body.as_str()
    }

    fn by_line(&self) -> &str {
        self.by_line.as_str()
    }

    fn key(&self) -> &str {
        self.key.as_str()
    }

    fn image(&self, context: &Context) -> Image {
        use crate::schema::swp_image::dsl;

        let query = dsl::swp_image
            .find(self.image_id);

        // let debug = debug_query::<diesel::pg::Pg, _>(&query);
        // println!("{:?}", debug);

       // how to get rid off N +1 query problem?
        
        let image = query.get_result::<Image>(&*context.connection)
            .expect("Error loading image");

        image
    }
}

#[juniper::object(
    Context = Context,
)]
impl Query {

    fn apiVersion() -> &str {
        "1.0"
    }

    fn articleCollection(context: &Context) -> FieldResult<Vec<Article>> {
        use crate::schema::*;
 
        let lh = executor.look_ahead();
        let res = lh.select_child("media");
        println!("{:?}", res);
        // I get some LookAheadSelection results here but not sure what to do with it now?
        let articles = swp_article::dsl::swp_article
            .load::<Article>(&*context.connection)
            .expect("Error loading posts");

        Ok(articles)
    }
}

This is the query I run:

{
  articleCollection {
    id
    slug
    title
    body
    lead
    publishedAt
    route {
      id
      name
    }
    media {
      id
      description
      located
      usageTerms
      body
      byLine
      key
      image {
        id
        assetId
        fileExtension
      }
    }
  }
  apiVersion
}

I would really appreciate your feedback.

[theduke]

Hey there. This is more of a general SQL question rather than Juniper specific.

The easiest solution here would be:

• first, load the articles in articleCollection, as you do now
• then, load all relevant items from the media table. How to do this with diesel is described here: https://docs.rs/diesel/1.4.2/diesel/associations/index.html
• add a field to Article that holds the media items (like media: Option<Vec<Media>>, and patch the loaded media rows into your articles
• in the Article::media resolver, check if self.media.is_some() and if so, just return the already present media items, and only execute a query if they are None.

You'd also need to to the same thing for the nested Image rows.

That way you can limit the whole request to 3 DB queries.

Lookahead

The lookahead functionality only comes in if you either want to limit the loaded fields to the fields requested by the user, or if you have additional arguments on the nested selections.

In that case you would use the information from look ahead to determine the requested fields and the filter arguments, so you can alter your database query accordingly.

Restricting the fields is somewhat awkward with Diesel, so you should probably stick to loading all fields. Also, you don't seem to have any arguments, so you would not need to use lookahead at all here.

[takeit]

Hi @theduke this is awesome! Thank you for the clarification. That's what I thought it should be done more on the db level but thought that lookahead is a magic thing here. Now I get this. I will optimize the queries based on your feedback.

[theduke]

PS: you might also want to take a look at https://github.com/davidpdrsn/juniper-eager-loading. I haven't used it yet, and it is still new and experimental, but is aims to make exactly this problem easier.

I'm closing this, but feel free to ask more questions here.

[davidpdrsn]

@takeit If so you might also enjoy https://github.com/davidpdrsn/graphql-app-example. It is a complete example app I maintain that uses the exact stack you mention plus https://github.com/davidpdrsn/juniper-from-schema.

The setup is very close to what we do at @tonsser and its working well for us.

[takeit]

@davidpdrsn this looks great! Thanks! It should be linked to the readme file in the Juniper repository.