Complex query

[chlebta]

Sorry for posting this issue here but it's urgent :

I need help for creating an complexe query:

First here's a schema for my db :

• The friends model represent the friendship between two users.

• Each user has a city

What I'm trying to achieve is to create function that take an userId as input and return list of cities Sorted by number of friends.

Here's my code until now

    function getCities(userId) {
    
      //This query allow to count number of friends in a given city 
      var innerQuery1 = new Parse.Query(Friends);
      innerQuery1. equalTo("user1", userId);
    
      var innerQuery2 = new Parse.Query(Friends);
      innerQuery2.equalTo("user2", userId);
    
      countFriendsQueryInCity = Parse.Query.or(innerQuery1, innerQuery2);
      countFriendsQueryInCity.equalTo("city", cityId)
      countFriendsQueryInCity.count({..})
    
      //This to get all cities
      var query = new Parse.Query(City);
     //Sort this query using the first one 
      query.find({})
      
    }

So the probleme is I can't figure a way way in parse or mongodb to join the two queries ?

[flovilmart]

we don't provide code level support on this repository, please use Stackoverflow.

[chlebta]

@flovilmart thank you ! another question is it possible to migrate form mongodb to postgres ?
And is parse + postgres stable ?

[flovilmart]

We don't provide migration from mongoDB to postgres, but you can probably use the ParseObject representation to make it happen. I will not enter the details of such procedure as not supported at the moment.

@kulshekhar is running with postgres and I don't think he has seen any major issue.

[kulshekhar]

@chlebta I've been using parse with postgres for a few months and I haven't faced any postgres specific issues at all. The setup has an issue with memory leakage but I don't think that has anything to do with postgres.

[chlebta]

@kulshekhar I' thinking to migrate vers postures because it provide more consistance and also it support complex query (like join and much more)
So I need you're opinion about that is it the right decision ?
Also is possible to query the postgres db directly from parse then convert the result to pfobejct ? (Since pfquery doesn't support join query)

[kulshekhar]

@chlebta

The key thing is that if you want to execute queries that can't be handled by parse natively, you're going to need another server to connect to your database and execute those queries. Now this server can be public facing or, as in my case, serve as a local service which gets consumed from cloud code. You'll also need to ensure that this part honors the permissions structure your app has.

Now you have to decide whether you want to maintain two servers, one parse and one your own. Remember that you'll have to manage the scaling/availability of both.

In my case I needed transactions but that comprised a small part of the entire app (less than 5% in terms of LOC, maintenance overhead) so it made sense to stick with parse and add a new service.

If the maintenance requirements for the external service would've been much higher as a proportion, I wouldn't have chosen parse for this app.

As to your last question, I don't have any experience using parse with android/ios but I don't see why you couldn't convert the result to the format you need. Parse server returns JSON. As long as your custom endpoints/queries return data in the right shape, it shouldn't be a problem.

[chlebta]

@kulshekhar
what I meant is : inside the server is it possible to write code in nodjs to interrogate the postgres db and then convert the result to parse objects?
The cloud function it will allow only to communicate between server and mobile.

Because all what I need is in some cases I've to use join table !! like in my example below

[kulshekhar]

@chlebta yeah, that's possible. You can mount parse-server on a regular express app

cloud code can do anything you want it to do. You can call an external service from there. You could also create a connection and connect to the database from there if you want to (but it would be a terrible way to do this!)

[chlebta]

@kulshekhar I know it's a terrible idea but I can't find another way to do it ? any help for this one ?

[kulshekhar]

@chlebta I meant establishing a connection to the database from cloud code is a terrible idea. The rest of it (mounting parse on an express app, external service, etc) is fine!

I was just trying to illustrate that cloud code isn't really limited to the functionality that was offered on the hosted service.

[chlebta]

@flovilmart @kulshekhar I've another question about postgres.
I've noticed that pointer are not created as foreign key, why ? and if I change them to foreign could this cause trouble ?

[flovilmart]

Because that would imply different resolution logic between mongodb and Postgres. Foreign keys, joins etc... don't exist in mongodb, therefore, it wasn't implemented as SQL constructs. Those are resolved at runtime.

[kulshekhar]

@chlebta to add to what @flovilmart said, if your schema is stable and if you don't expect to drop/modify a column from the dashboard/parse API, adding a foreign key shouldn't be a problem.

I haven't done this but I don't see any harm in trying this out (let us know how it works out!)

[flovilmart]

To he different that a pointer is a string worth the form _p_<className>_<objectid> IIRC