Can't manage to query on differents fields at the same time

SQL++
1

Hello everyone,

as inner/left join are not implemented with couchbase, i’m having some trouble to get a propre sql request which do what I want.

My bucket is : suivis

Here is what my documents looks like :

{
  "date": "2017-11-15T09:46:39", // ISO 8601 
  "_class": "com.ms.sm.msdb.entite.Suivi", // using springboot
  "user": {
    "code": "XXX",
    "idSoc": yyyyy,
    "archive": false,
    "id": yyyyyyy,
    "lastname": "XXX",
    "firstname": "XXX"
  },
  "mission": {
    "intitule": "XXX",
    "id": yyyyyy
  }
}

What I want to do is to retrieve for each user and for each mission, to get the last document which is at least 3 months older using the date attribute.
I managed to get the document for each collaborateur, but I can’t manage to do it for each mission…

Here is my SQL query :
Spring boot :
- @Query( "#{#n1ql.selectEntity} WHERE #{#n1ql.filter} and abs(DATE_DIFF_STR(date, now_str(), 'month')) >= 3 ORDER BY date desc LIMIT 1" )

And in pure SQL :

- `SELECT * FROM suivis where abs(DATE_DIFF_STR(date, now_str(), 'month')) >= 3 ORDER BY date desc LIMIT 1`

I thought that something like that should help me, but I never manage to do it…
- select * from suivis s1 where s1.om = (select distinct(om) from suivis s2 where s1.om = s2.om) and abs(DATE_DIFF_STR(s1.date, now_str(), 'month')) >= 3 ORDER BY s1.date desc LIMIT 1

Can someone help me to go through my issue ?

Thanks

2

Could you please little clear on the problem.

Not sure what is last SQL statement and where is s1.om

3

If user.id, mission.id differentiate for different user and different mission

CREATE INDEX ix1 ON suivis(date);

SELECT MAX([d.date,d])[1] AS doc FROM suivis AS d 
 WHERE d.date < DATE_ADD_STR(NOW_STR(),-3,'month')
GROUP BY  d.`user`.id,  d.mission.id;
4

Oh yes, my bad. “om” is “mission”.
Just forget to replace it for the example when I simplified it.

5

1 user can have multiples document with differents missions.

For example :

[
{
  "date": "2017-11-15T09:46:39", // ISO 8601 
  "_class": "com.ms.sm.msdb.entite.Suivi", // using springboot
  "user": {
    "code": "TEST",
    "idSoc": 1,
    "archive": false,
    "id": 1,
    "lastname": "TestLastName",
    "firstname": "TestFirstName"
  },
  "mission": {
    "intitule": "mission1",
    "id": 1
  }
},
{
  "date": "2017-12-15T15:10:35", // ISO 8601 
  "_class": "com.ms.sm.msdb.entite.Suivi", // using springboot
  "user": {
    "code": "TEST",
    "idSoc": 1,
    "archive": false,
    "id": 1,
    "lastname": "TestLastName",
    "firstname": "TestFirstName"
  },
  "mission": {
    "intitule": "mission1",
    "id": 1
  }
},
{
  "date": "2017-12-03T10:10:10", // ISO 8601 
  "_class": "com.ms.sm.msdb.entite.Suivi", // using springboot
  "user": {
    "code": "TEST",
    "idSoc": 1,
    "archive": false,
    "id": 1,
    "lastname": "TestLastName",
    "firstname": "TestFirstName"
  },
  "mission": {
    "intitule": "mission2",
    "id": 2
  }
}
]

So what I really want here is to get the latest document which is at least 3 months older, for each user, and for each mission.

In my example above, it should return me the second and the last document.

I’m not at work until monday but I will try your SQL request and gonna update this topic asap.

Thank’s for your answer

6

The query in the post#2 will give the output you are looking for.

7

It works, thanks :slight_smile: