MongoDB : Sort by or Order the documents in a collection by timestamp

[juhibhatia]

Hi,

I need to sort or order by the documents on timestamp and get the first document where the "tagName" = "water temperature". The query i have written is not solving the purpose. The POJO for the document i am storing in the datastore is as follows:

public class MongoTag {
	
	private String tagName;
	private String value;
	private DataType dataType;
	private long timestamp;
	
	public MongoTag() {
	}
	
	public MongoTag(String tagName, String value, DataType dataType, long timestamp) {
		this.tagName = tagName;
		this.value = value;
		this.dataType = dataType;
		this.timestamp = timestamp;
	}

	public String getTagName() {
		return tagName;
	}
	public void setTagName(String tagName) {
		this.tagName = tagName;
	}
	public String getValue() {
		return value;
	}
	public void setValue(String value) {
		this.value = value;
	}
	public DataType getDataType() {
		return dataType;
	}
	public void setDataType(DataType dataType) {
		this.dataType = dataType;
	}
	public long getTimestamp() {
		return timestamp;
	}
	public void setTimestamp(long timestamp) {
		this.timestamp = timestamp;
	}
}

I am writing a junit test suite to test the scenario. Following is the piece of code I have wrotten:

public class MongoTagDaoTest extends TestCase {
	
	private static Logger logger = Logger.getLogger(MongoTagDaoTest.class);
	private MongoTemplate mongoTemplate;
	private static String Collection = "tagtest";
	private DBCollection dbCollection;
	private MongoTag mongoTagTemp = new MongoTag("water temperature", "100", DataType.INT, System.currentTimeMillis() - 5000);
	private MongoTag mongoTagPh = new MongoTag("water ph", "7.5", DataType.FLOAT, System.currentTimeMillis());
	private MongoTag mongoTag = new MongoTag("water temperature", "120", DataType.INT, System.currentTimeMillis());
	
	public static Test Suite() {
		return new TestSuite(MongoTagDaoTest.class);
	}

	protected void setUp() throws Exception {
		ApplicationContext applicationContext = new GenericXmlApplicationContext("testmongodb.xml");
		mongoTemplate = (MongoTemplate) applicationContext.getBean("mongoTemplate");
		BasicConfigurator.configure();
		dbCollection = mongoTemplate.getCollection(Collection);
	}
	
	public void testAddMongoTag() {
		logger.info("inserting MongoTagTemp");
		mongoTemplate.insert(Collection, mongoTagTemp);;
		logger.info("inserting mongoTagTemp: " + mongoTagTemp);
		logger.info("mongoTagTemp: " + mongoTagTemp);
		logger.info("mongoTagTemp tagName: " + mongoTagTemp.getTagName());
		logger.info("mongoTagTemp timestamp: " + mongoTagTemp.getTimestamp());
		logger.info("mongoTagTemp value: " + mongoTagTemp.getValue());
		logger.info("mongoTagTemp dataType: " + mongoTagTemp.getDataType());
		
		logger.info("inserting MongoTagPh");
		mongoTemplate.insert(Collection, mongoTagPh);
		logger.info("inserting MongoTag: " + mongoTagPh);
		logger.info("mongoTagPh: " + mongoTagPh);
		logger.info("mongoTagPh tagName: " + mongoTagPh.getTagName());
		logger.info("mongoTagPh timestamp: " + mongoTagPh.getTimestamp());
		logger.info("mongoTagPh value: " + mongoTagPh.getValue());
		logger.info("mongoTagPh dataType: " + mongoTagPh.getDataType());
		
		logger.info("inserting MongoTag");
		mongoTemplate.insert(Collection, mongoTag);
		logger.info("inserting mongoTag: " + mongoTag);
		logger.info("mongoTag: " + mongoTag);
		logger.info("mongoTag tagName: " + mongoTag.getTagName());
		logger.info("mongoTag timestamp: " + mongoTag.getTimestamp());
		logger.info("mongoTag value: " + mongoTag.getValue());
		logger.info("mongoTag dataType: " + mongoTag.getDataType());
	}
	
	public void testGetMongoTag() throws MongoDBDataNotFoundException {
		logger.debug("Read MongoTag");
		
		Query query = new Query(Criteria.where("tagName").is("water temperature"));
		logger.info("Executing Query: Criteria.where(tagName).is(water temperature)");
		query.sort().on("timestamp", Order.DESCENDING);
		logger.info("Executing query: query.sort().on(timestamp, Order.DESCENDING)");
		MongoTag mongoTags = mongoTemplate.findOne(Collection, query, MongoTag.class);
		
		logger.info("mongoTags: " + mongoTags);
//		assertEquals(mongoTagTemp, mongoTags);
		
		logger.info("mongoTags tagName: " + mongoTags.getTagName());
		assertEquals(mongoTagTemp.getTagName(), mongoTags.getTagName());
		
		logger.info("mongoTags timestamp: " + mongoTags.getTimestamp());
//		assertEquals(mongoTagTemp.getTimestamp(), mongoTags.getTimestamp());
		
		logger.info("mongoTags value: " + mongoTags.getValue());
		assertEquals(mongoTagTemp.getValue(), mongoTags.getValue());
		
		logger.info("mongoTags dataType: " + mongoTags.getDataType());
		assertEquals(mongoTagTemp.getDataType(), mongoTags.getDataType());
	}
	
//	public void testDropCollection() {
//		mongoTemplate.dropCollection("tagtest");
//		logger.info("Drop Collection");
//	}
}

The code in red and bold letters is the code that does not give the expected results. May be I am querying the datastore incorrectly, b'coz it does not execute the sort method. Please help for the correct syntax in case.

Thanks,
Juhi Bhatia.

[Oliver Gierke]

Would be cool if you added assertions to demonstrate what is actually expected. Beyond that it would be interesting what is returned as well. Lowering the log level to debug should print you the actual query generated to the log.

[juhibhatia]

Hi Oliver,

Thanks for your reply. The actual problem is the Query. Can you please suggest me how should i query using find() and sort() together. Somehow I am not able to apply sort on the results i need. May be I am not aware of the syntax. The query in SQL that i need to map it to mongodb using spring-data is :

Select * from MongoTag where tagName = "water temperature" order by timestamp desc;

Any suggestions will be helpful. I am using spring-data-mongodb 1.0.0.M2.

Thanks,
Juhi Bhatia.

[Oliver Gierke]

I can just repeat. Please add some logging output to the thread here because otherwise it's hard to diagnose what Mongo query you build. Beyond that you're using a very dated version of our library so I suggest you try M4 and see whether the problem persists.