Select Data and put them in a nested structure

1

Hello everyone,

currently I’m working on a Rocket REST API and I use Diesel for my database.
For simplicity I use the JSON format to show my problem.

When I make a GET requests I request an event that has a many to many relationship with a discipline. The output that I get from my query is something like:
{
event: “super indoor event”,
discipline: “dart”
}
{
event: “super indoor event”,
discipline: “football”
}

The structure that I really want is
{
event: “super indoor event”,
discipline: [“dart”, “football”],
}

The object that I currently use to load the data is the following:
#[derive(Queryable, Debug, Serialize)]
pub struct QueryResult {
pub event: String,
pub discipline: String,
}

I’m wondering if it’s doable to achieve something like:
#[derive(Queryable, Debug, Serialize)]
pub struct QueryResult {
pub event: String,
pub discipline: Vector<DisciplineResult>,
}

where the Vector contains multiple disciplines.

At the moment I use the following logic to load the data:

match join.load::<QueryResult>(connection) {
            Ok(result) => Ok(result),
            Err(_) => Err(()),
        }

But this doesn’t work of course when I include the Vector.

I found this discussion: Link to Diesel Forum but I don’t know how to use the grouped by in this context.

I really appreciate your help.

2

Please try to correctly format your code, otherwise it’s really hard for other people to read it.

3

Without knowing how your database structure looks like there is little I can help you with. What you are describing looks more like a task for aggregation after fetching the data.

If you want to make the database do the work and grouping you should first develop the SQL query (thereby the aggregation logic) and then put this into a calls to diesel. This looks a task for a query like (without having tested this):

SELECT event, GROUP_CONCAT(DISTINCT discipline SEPARATOR "|") as disciplines from events where event = "super indoor event" GROUP BY event

You can load this, map the result from (String, String) to QueryResult by implementing From<(String, String)> for QueryResult.

The same applies to grouped_by which returns a list of tuples which maps a set of child records related through a foreign key to a parent record with a matching primary key.

The discussion I started was not about aggregation but about fetching relations automatically and assign the results to fields in the result struct.

If you need more help please provide more information, at least your input data, and please format your code.