How to use multiple select in one query?

Question

I'm going to write this query, it works in Console.sql or in Workbench, but when I use it in @Query I get an error of syntax!

unexpected token: LIMIT


@Query("select w.name ,
                (select d.name 
                from document d 
                where w.id = d.workspace.id 
                order by (d.id) DESC 
                LIMIT 1 
                ) as nameLastDoc  
        from Workspace w  
            left join UserWorkspace u on u.workspace.id = w.id 
            left join Document d on w.id = d.workspace.id 
        group by (w.id)")

    List<?> findAllWorkspaces();

In my IDE the error is in the next select select w.name ,(select d.name ...

`d.workspace.id ` ? Is that right? or is it `d.workspace_id` — RiggsFolly, Jul 05 '21 at 19:06
Why should I provide a [Minimal Reproducible Example for any SQL query question?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — RiggsFolly, Jul 05 '21 at 19:11

score 1 · Answer 1 · 2021-07-05T21:11:29.027

As far as I worked with JPA Repository, it's quite limited (compared to a direct query to the database)

You can make a query directly in the Controller / where is your method :

// Autowiring the JdbcTemplate so we can send queries to the database
@Autowired
JdbcTemplate jdbcTemplate;

// Mock method that returns what it got from the database
public List<Map<String, Object> findAllWorkspaces() {
String query = "select w.name ,
            (select d.name 
            from document d 
            where w.id = d.workspace.id 
            order by (d.id) DESC 
            LIMIT 1 
            ) as nameLastDoc  
    from Workspace w  
        left join UserWorkspace u on u.workspace.id = w.id 
        left join Document d on w.id = d.workspace.id 
    group by (w.id)";

return jdbcTemplate.queryForList(query);
}

It returns a list of Map<String, Object>, where the string is the column's name, and the Object is the value from the database.

I used this in my application because I had to make custom queries and it worked. If this doesn't work either, your selection query is broken.

score 0 · Answer 2 · answered Jul 05 '21 at 19:06

0

I dont know the exact error you are getting but I faced an error while using limit in JPA query. You can try removing it once to see if that is the issue. Also refer this thread.

answered Jul 05 '21 at 19:06

Tarun Thapa

75
9

Impossible in my case to remove it, because it returns more than 1 row. – munir mounir Jul 05 '21 at 19:15

How to use multiple select in one query?

2 Answers2