Using a join query in Raven DB
1,090 views
Skip to first unread message
avin
Sep 17, 2012, 9:27:35 AM9/17/12
to rav...@googlegroups.com
hi,
I have a User collection and a Portfolio collection mentioned below. The Id of the Portfolio is passed as a foreign Key to the Users collection.
User
=========
{
"FirstName": "James",
"LastName": "Bond",
"EMailAddress": "",
"Password": "",
"CustomerId": "customers/65",
"Portfolios": [
{
"Id": "portfolios/65",
"IsDefault": true
}
],
"CreatedUserName": ""
}
Portfolios
===========
{
"Title": "Maths",
"Description": "Maths Descr",
"CustomerId": "customers/65",
"IsDefault": false,
"CreatedUserName": "All P",
"UpdatedUserName": "All P",
"CreatedDate": "2012-09-06T04:08:18.2876843Z",
"LastUpdatedDate": "2012-09-06T04:08:18.2876843Z",
"Version": "Latest"
}
I am using the following query to get the user details. Is there any means so that I can get the Portfolio name also from
the users collection using the below mentioned query. Currently there is only the Id of the portfolio in the users collection.
/// <summary>
/// Initializes a new instance of the UserSearchIndex class.
/// </summary>
public UserSearchIndex()
{
this.Map = users => from user in users
from portfolio in user.Portfolios
select new
{
Query = new
{
user.FirstName,
user.LastName,
user.EMailAddress
},
user.Id,
user.CustomerId,
UserPortfolio = user.Portfolios
};
this.Index(x => x.Query, FieldIndexing.Analyzed);
}
Any help will be deeply appreciated.
Regards,
Avinash
Felipe Leusin
Sep 17, 2012, 9:55:31 AM9/17/12
to rav...@googlegroups.com
You would have to use a MultiMap for this.
Or you could load the User including the Portfolio Ids ( Session.Include<User>(x => x.Portfolios.Id).Load(userId) ) and then Load the portfolios (they will already be in memory).
avin
Sep 17, 2012, 10:15:17 AM9/17/12
to rav...@googlegroups.com
thx
Felipe,
I have to go with multimap, as this is a requirement based on user search in my application. but I am not sure abt hw to write the Multi map. this my code, , not fully complete.
/// <summary>
/// The class that defines an index for doing full text search in the users.
/// </summary>
public class UserSearchIndexNew : AbstractIndexCreationTask<User, UserSearchIndexNew.Result>
{
/// <summary>
/// The class that maps the fields in Users document for doing a full text search.
/// </summary>
public class Result
{
/// <summary>
/// Gets or sets the query for the index for searching the users.
/// </summary>
public string Query { get; set; }
/// <summary>
/// Gets or sets the Id of the user related to.
/// </summary>
public string Id { get; set; }
/// <summary>
/// Gets or sets the Id of the customer the user related to.
/// </summary>
public string CustomerId { get; set; }
/// <summary>
/// Gets or sets the Portfolio.
/// </summary>
public string Portfolio { get; set; }
/// <summary>
/// Gets or sets the Role.
/// </summary>
public RoleType Role { get; set; }
/// <summary>
/// Gets or sets the User Status.
/// </summary>
public UserStatus UserStatus { get; set; }
/// <summary>
/// Gets or sets a value indicating whether the Active status.
/// </summary>
public bool Active { get; set; }
/// <summary>
/// Gets or sets the PortfolioData.
/// </summary>
public PortfolioData UserPortfolio { get; set; }
/// <summary>
/// Gets or sets the PortfolioData.
/// </summary>
public Portfolio UserPortfolioTitle { get; set; }
}
/// <summary>
/// Initializes a new instance of the UserSearchIndexNew class.
/// </summary>
public UserSearchIndexNew()
{
this.Map = users => from user in users
select new
{
user.FirstName,
user.LastName,
user.EMailAddress,
user.Id,
user.CustomerId,
user.CreatedDate,
Role = user.Roles.Select(x => x.Type),
Portfolio = user.Portfolios.Select(x => x.Id),
UserPortfolio = user.Portfolios,
UserStatus = user.UserStatus,
Active = user.Active
};
this.Map = portfolios => from portfolio in portfolios
select new
{
portfolio.Title
};
}
}
Any idea on this pls.........
Regards,
Regards,
Avinash
Matt Warren
Sep 17, 2012, 11:25:37 AM9/17/12
to rav...@googlegroups.com
I might be easier to use the Load approach that Felipe also mentioned. Multi-Map is a bit messy for this scenario as you have no overlapping/common fields between the 2 entities (http://ayende.com/blog/89089/ravendb-multi-maps-reduce-indexes)
So just do a query against the User docs and the include in the results the relevant Portfolio doc, by using the Id stored in the User (http://ravendb.net/docs/faq/optimizing-referenced-documents-load)
Felipe Leusin
Sep 17, 2012, 3:28:49 PM9/17/12
to rav...@googlegroups.com
I wrote this in kind of a hurry but yeah. I would recommend using the Load/Include approach unless you need to query fields in the Portoflio for the search.
avin
Sep 18, 2012, 1:19:14 AM9/18/12
to rav...@googlegroups.com
Ok Felipe. So as of now what will be include/load query to get the user details along with the Portfolio name from this two collections.
User
=========
{
"FirstName": "James",
"LastName": "Bond",
"EMailAddress": "",
"Password": "",
"CustomerId": "customers/65",
"Portfolios": [
{
"Id": "portfolios/65",
"IsDefault": true
}
],
"CreatedUserName": ""
}
Portfolios
===========
{
"Title": "Maths",
"Description": "Maths Descr",
"CustomerId": "customers/65",
"IsDefault": false,
"CreatedUserName": "All P",
"UpdatedUserName": "All P",
"CreatedDate": "2012-09-06T04:08:18.2876843Z",
"LastUpdatedDate": "2012-09-06T04:08:18.2876843Z",
"Version": "Latest"
}
I am not getting a clear answer for this. Help please......
Regards,
Avinash
Matt Warren
Sep 18, 2012, 5:04:38 AM9/18/12
to rav...@googlegroups.com
something like this:
var users = session.Query<User>()
.Where(x => x.FirstName == "James")
.Include(x => x.Portfolios.Select(po => po.Id)
.ToList();
var firstUser = users.FirstOrDefault();
foreach (portfolio in firstUser.Portfolios)
{
//This line won't need to go to the server to fetch the portfolio, it's already been "Included"
var portfolio = session.Load<Portfolio>(portfolio.Id);
Reply all
Reply to author
Forward
0 new messages