How to query by relationship?

[PatrickHuetter]

Is it possible to query by relationship (using spring-data-mongodb)?

I've an Item Model and an Item belongs to a Category. I would like to query the items by category.id=={id}.

I already added these path mappings but the results are always empty.

.put("category", QProduct.item.category)
.put("category.id", QProduct.item.category.id)

[vineey]

I'm not sure if i understand you. There is no relational association existing in mongodb. Basically, using spring-data-mongodb, you can only query in a single collection at a time. But it does allow json like query, like querying against nested fields.

With regards to your model, make sure the mongodb collection for ITEM really contains document with category data, such as:

{
   itemName: 'Some Item Name',
   otherItemAttributes: ... 
   category : {
       id : 1
   }
} 

[PatrickHuetter]

That sounds good, so i could query for the category id. The problem is, i'm using DBref for the category, so the underlying Item document looks like this:

{
"itemName": "Some Item Name",
 "category" : DBRef("category", ObjectId("57b85e9ec9e77c000169d3fa"))
}

How can i query this?

[vineey]

Checking on some reference, this is possible with querydsl, please check this link

http://stackoverflow.com/questions/33211645/spring-data-mongo-how-to-query-by-dbref-fields-id

You just have to annotate category field by @DBREF. The generated Q class should be able to identify and support this automatically.

The querydsl github issue ticket for this one was this, querydsl/querydsl@350a1a4

The Spring Data issue ticket for this can be found here, https://jira.spring.io/browse/DATAMONGO-825

Please let me know if it works.

[vineey]

@PatrickHuetter will add this scenario in my test cases

[vineey]

@PatrickHuetter I found the issue. I have to create a separate join builder for @DBREF fields.

querydsl/querydsl#113

[PatrickHuetter]

That would be very cool! 👍

[vineey]

@PatrickHuetter Hi Pat, FYI this will be included in 3.0.0 release, which includes major refactoring.
We are going to split querydsl into two separate modules, one for Mongodb and another for JPA for modularity and maintainability. Also we are going to make it easier to use the api via DSL factory style to shield user from different ContextBuilder and Visitor implementations. Just have to pass the rql string and parameters on the same DSL api. Please bear with me.
Thanks for your feedback.

[PatrickHuetter]

Hi @vineey, is there something new on this? I would be glad to hear from you.

[vineey]

@PatrickHuetter Sorry man, little busy with my work lately. Hope to finish the refactoring and DSL builder this weekend. By then, I can proceed with Mongodb @DBREF feature. I can also delegate the other feature to the others who are available to help.

[vineey]

@PatrickHuetter This is in progress now

[vineey]

@PatrickHuetter @pacitu upon checking different use cases, we have limitations and issues. Since querydsl uses joins on predicates for DBRef, this means we have to split rql filter expressions and separate those refer to DBRef from those that refers to the original collection.

From querydsl issue, querydsl/querydsl#113

@Test
public void Double2() {
    assertEquals("Mike", where()
            .join(user.friend(), friend).on(friend.firstName.eq("Mary"))
            .join(user.enemy(), enemy).on(enemy.firstName.eq("Ann"))
            .singleResult().getFirstName());
}

If we have,

class Name {
  private Long id;

 private String firstname;
 private String middlename;
 private String lastname;
}

class Account {

  private String username;

  @DBRef
  private Name name;
}

We can't do this,

(account.username == 'Jo*' or account.name.firstname == 'John') and (account.username == 'Smith' or account.name.lastname == 'Smith')

So the limitation is we can't do combination of AND/OR logical expression between DBRef field predicate and the original collection field predicate. This means its either we use only DBRef fields as predicates or vice versa. Is it clear?

Do you think this is enough or do you have any suggestion.

[PatrickHuetter]

I hope that i've understand that correctly. So it would be possible to query by identifier (equal)?

For example:
product.category == ObjectId('asdafsfgdfgdfsgdsfgfd...') or similar? while category within product is annotated with @DBREF.

[vineey]

@PatrickHuetter Yup it is possible. But the problem lies if we mix it with other expressions like

product.id == 1 or product.category.id ==2.

The first one may be represented

where(QProduct.product.id.eq(1)).

then the second one with the DBRef can be represented as,

where()
.join(QProduct.product.category, QCategory.category).on(category.id.eq(1))

So the problem is how can we represent the OR, remember the complete expression is

product.id == 1 or product.category.id ==2.

This is the problem we are facing given the nature of MongoDB.

[PatrickHuetter]

@vineey At first it would be a step further if only one part of the expression would work but i can't get either of them working. How should the expression look like? I can't query by mongodb id. I already tested id==57547374c9e77c000109a005 and id==ObjectId("57547374c9e77c000109a005") and so on. None of them returns a positive result set although the object with the given id exists in the database/collection. Which version of the library should i use?

[vineey]

@PatrickHuetter nope its not yet implemented. Expression for DBRef needs a new implementation by using querydsl join. I am just showing the possible issues if we continue implementing it. This feature seems impossible to be implemented correctly due to mongodb's limitation, there no possibke workarounds. It is not meant for this one.

[vineey]

@PatrickHuetter I believe, you just have to create a workaround.