Using result of one subquery in another subquery


(bgossey) #1

Hey,

Is the following query possible with elastic search:

I have two types: company and category

  • Company is a type containing fields like name, phone, keywords,
    location… and also a special field called category-id which contains
    an id (number)

company {
name:truvo,
phone:(0032)123.32.42
keywords: yellow pages, white pages

category-id: 1234
}

  • Category is a type containing an id (the category id mentioned
    above) and a set of names for that category

category {
category-id:1234
names: software, applications
}

Now I want to do a query: give me all companies where the category is
software and it should return the list of companies in this category
(so it would include Truvo). ~ a join in sql

Of course I can merge the two types before indexing, but keeping it in
two separate types provides me the flexibility to update the listings
(for example adding a synonym computer programs to the category 1234)
without the need to reindex the companies.

I could of course do this also by doing two queries (the first one to
retrieve the categories, because it could be more than one category)
and then doing a second query using the result of the first query, but
I would like (id possible) to do this in one query.

Any suggestions?

thanks,
Bert


(Shay Banon) #2

Both options you mentioned are sound. The other option is possibly to use
parent child feature, but note that a in parent child relationship is one
(parent) to many (children).

On Thu, Apr 5, 2012 at 2:05 PM, bert gossey bgossey@hotmail.com wrote:

Hey,

Is the following query possible with elastic search:

I have two types: company and category

  • Company is a type containing fields like name, phone, keywords,
    location… and also a special field called category-id which contains
    an id (number)

company {
name:truvo,
phone:(0032)123.32.42
keywords: yellow pages, white pages

category-id: 1234
}

  • Category is a type containing an id (the category id mentioned
    above) and a set of names for that category

category {
category-id:1234
names: software, applications
}

Now I want to do a query: give me all companies where the category is
software and it should return the list of companies in this category
(so it would include Truvo). ~ a join in sql

Of course I can merge the two types before indexing, but keeping it in
two separate types provides me the flexibility to update the listings
(for example adding a synonym computer programs to the category 1234)
without the need to reindex the companies.

I could of course do this also by doing two queries (the first one to
retrieve the categories, because it could be more than one category)
and then doing a second query using the result of the first query, but
I would like (id possible) to do this in one query.

Any suggestions?

thanks,
Bert


(Ray Ward-2) #3

Hi kimchy,

Could you give an example of how to perform this all in one query? Or is
that not actually possibly?

Thanks

On Monday, April 9, 2012 2:58:01 AM UTC+9, kimchy wrote:

Both options you mentioned are sound. The other option is possibly to use
parent child feature, but note that a in parent child relationship is one
(parent) to many (children).

On Thu, Apr 5, 2012 at 2:05 PM, bert gossey <bgo...@hotmail.com<javascript:>

wrote:

Hey,

Is the following query possible with elastic search:

I have two types: company and category

  • Company is a type containing fields like name, phone, keywords,
    location… and also a special field called category-id which contains
    an id (number)

company {
name:truvo,
phone:(0032)123.32.42
keywords: yellow pages, white pages

category-id: 1234
}

  • Category is a type containing an id (the category id mentioned
    above) and a set of names for that category

category {
category-id:1234
names: software, applications
}

Now I want to do a query: give me all companies where the category is
software and it should return the list of companies in this category
(so it would include Truvo). ~ a join in sql

Of course I can merge the two types before indexing, but keeping it in
two separate types provides me the flexibility to update the listings
(for example adding a synonym computer programs to the category 1234)
without the need to reindex the companies.

I could of course do this also by doing two queries (the first one to
retrieve the categories, because it could be more than one category)
and then doing a second query using the result of the first query, but
I would like (id possible) to do this in one query.

Any suggestions?

thanks,
Bert

--


(Jörg Prante) #4

Hi Ray,

have you thought of dropping "category-id" and just use a filter query on
the company index looking for the "category" as a string?

{
"company": {
"name" : "truvo",
"category" : [ "software", "applications", ... ]
}
}

This process is called denormalization. Data fields like "category-id" are
useful for relational queries, but are "poison" for efficient document
retrieval.

For parent/child approach, you have to index categories as parents, and
hook companies to a single category (which is obviuosly not what you asked
for). Think of parent/child as building a tree, not a table.

Jörg

On Monday, October 15, 2012 5:04:14 AM UTC+2, Ray Ward wrote:

Hi kimchy,

Could you give an example of how to perform this all in one query? Or is
that not actually possibly?

Thanks

On Monday, April 9, 2012 2:58:01 AM UTC+9, kimchy wrote:

Both options you mentioned are sound. The other option is possibly to use
parent child feature, but note that a in parent child relationship is one
(parent) to many (children).

On Thu, Apr 5, 2012 at 2:05 PM, bert gossey bgo...@hotmail.com wrote:

Hey,

Is the following query possible with elastic search:

I have two types: company and category

  • Company is a type containing fields like name, phone, keywords,
    location… and also a special field called category-id which contains
    an id (number)

company {
name:truvo,
phone:(0032)123.32.42
keywords: yellow pages, white pages

category-id: 1234
}

  • Category is a type containing an id (the category id mentioned
    above) and a set of names for that category

category {
category-id:1234
names: software, applications
}

Now I want to do a query: give me all companies where the category is
software and it should return the list of companies in this category
(so it would include Truvo). ~ a join in sql

Of course I can merge the two types before indexing, but keeping it in
two separate types provides me the flexibility to update the listings
(for example adding a synonym computer programs to the category 1234)
without the need to reindex the companies.

I could of course do this also by doing two queries (the first one to
retrieve the categories, because it could be more than one category)
and then doing a second query using the result of the first query, but
I would like (id possible) to do this in one query.

Any suggestions?

thanks,
Bert

--


(system) #5