Compound Keys problem


#1

Hi,

I have a view with compound key with 4 columns. one of column is Date in string.

View

function (doc, meta) {
emit([parseInt(doc.loc_no), parseInt(doc.host_no), parseInt(doc.pool_id), doc.race_date], parseFloat(doc.netsales));
}

Json document

{
“host_no”: “368”,
“race_no”: “3”,
“reg_host”: “368”,
“netsales”: “445.00”,
“loc_no”: “301”,
“race_date”: “2012-04-01”,
“pool_id” : “1”

}

startkey [319,0,1,“2012-01-01”]
endkey [319,1000,100,“2012-01-31”]

group_level 3.

View is ignoring date search(last column in Key). Kindly tell me how to include date in search.


#2

When I did a startKey of [0,0,0,“2012-01-01”] based on the document values you provided above. This resulted in the ability to group by 1,2,3 or 4 for the full value:

group level 3:
[301,368,1]

group level 4:
[301,368,1,“2012-04-01”]

I suggest modifying the start key a bit and seeing if you get a better result?
Don’t forget to add a reduction as well and save the view. I used the built-in _count reduction.


#3

Hi,

Your scenarios are good. But I need it by group 3 and keys with date.

Thanks


#4

Any Help on this would be helpful to me.


#5

You have 4 values returned, with the date being the last returned value. In order to group them by level 3, the date would have to be the 3rd value, but then you would have to choose what would not not return for the 4th level or what would you simply not include. Having said that, you could construct your view so that 2 of the values are combined as a string but separated by a “,” so as to be able to group at 3 levels and still see 4 values at the application.

Beware though that one pair would be concatenated as a string if you chose to do this.

function (doc, meta) {
emit([[parseInt(doc.loc_no), parseInt(doc.host_no)], parseInt(doc.pool_id), doc.race_date], parseFloat(doc.netsales));
}

Austin


#6

Hi Austin,

I tried to convert below query and I need my view results as result of query. Have a look and give me your suggestions.

select loc_no, host_no, pool_id, count(*), SUM(netsales) from summary where race_date < ‘2012-01-31’ and race_date > ‘2012-01-01’ and loc_no = 319 group by loc_no, host_no, pool_id order by host_no;

Thanks.


#7

It would be great help, if any one could see into this.


#8

Hi Gadipati,
So are you saying that you are trying to convert the SQL query into a View. If so I can take a look at this tomorrow.

If this is still development, you could take a look at N1QL as well, depending on your deployment date.

Austin