Revision 57269
Added by Michele Artini about 5 years ago
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/repository/readonly/OrganizationSimpleViewRepository.java | ||
|---|---|---|
| 16 | 16 |
// SEARCH |
| 17 | 17 |
Page<OrganizationSimpleView> findByNameContainingIgnoreCase(String name, Pageable pageable); |
| 18 | 18 |
|
| 19 |
// SEARCH FOR USER |
|
| 20 |
@Query(value = "select o.* from organizations_simple_view o left outer join user_countries uc on (uc.country = o.country) where o.name ilike %:text% and uc.email = :email", nativeQuery = true) |
|
| 21 |
Page<OrganizationSimpleView> findByNameForUser(String text, String email, Pageable pageable); |
|
| 22 |
|
|
| 19 | 23 |
// BROWSE BY COUNTRY |
| 20 | 24 |
@Query(value = "select country as value, count(*) as count from organizations group by country order by count desc", nativeQuery = true) |
| 21 | 25 |
List<BrowseEntry> browseCountries(); |
| ... | ... | |
| 40 | 44 |
+ "order by count desc;", nativeQuery = true) |
| 41 | 45 |
List<BrowseEntry> browseTypesForUser(String email); |
| 42 | 46 |
|
| 43 |
@Query(value = "select * from organizations_simple_view where ?1 = any(types)", countQuery = "select count(*) from types where type\\:\\:text = ?1", nativeQuery = true)
|
|
| 47 |
@Query(value = "select * from organizations_simple_view where ?1 = any(types)", countQuery = "select count(*) from types where type = ?1", nativeQuery = true) |
|
| 44 | 48 |
Page<OrganizationSimpleView> findByType(String type, Pageable pageable); |
| 45 | 49 |
|
| 50 |
@Query(value = "select o.* from organizations_simple_view o left outer join user_countries uc on (uc.country = o.country) where uc.email = ?2 and ?1 = any(o.types)", countQuery = "select count(*) from types t left outer join organizations o on (t.id = o .id) left outer join user_countries uc on (uc.country = o .country) where t.type = ?1 and uc.email = ?2", nativeQuery = true) |
|
| 51 |
Page<OrganizationSimpleView> findByTypeForUser(String type, String name, Pageable pageable); |
|
| 52 |
|
|
| 46 | 53 |
} |
| modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/controller/OrganizationController.java | ||
|---|---|---|
| 83 | 83 |
} |
| 84 | 84 |
|
| 85 | 85 |
@RequestMapping(value = "/search/{page}/{size}", method = RequestMethod.GET)
|
| 86 |
public Page<OrganizationSimpleView> search(@PathVariable final int page, @PathVariable final int size, @RequestParam final String q) {
|
|
| 87 |
return organizationSimpleViewRepository.findByNameContainingIgnoreCase(q, PageRequest.of(page, size)); |
|
| 86 |
public Page<OrganizationSimpleView> search(@PathVariable final int page, |
|
| 87 |
@PathVariable final int size, |
|
| 88 |
@RequestParam final String q, |
|
| 89 |
final Authentication authentication) {
|
|
| 90 |
return User.isSuperUser(authentication) |
|
| 91 |
? organizationSimpleViewRepository.findByNameContainingIgnoreCase(q, PageRequest.of(page, size)) |
|
| 92 |
: organizationSimpleViewRepository.findByNameForUser(q, authentication.getName(), PageRequest.of(page, size)); |
|
| 88 | 93 |
} |
| 89 | 94 |
|
| 90 | 95 |
@RequestMapping(value = "/byCountry/{code}/{page}/{size}", method = RequestMethod.GET)
|
| ... | ... | |
| 94 | 99 |
} |
| 95 | 100 |
|
| 96 | 101 |
@RequestMapping(value = "/byType/{type}/{page}/{size}", method = RequestMethod.GET)
|
| 97 |
public Page<OrganizationSimpleView> findByType(@PathVariable final String type, @PathVariable final int page, @PathVariable final int size) {
|
|
| 98 |
return organizationSimpleViewRepository.findByType(type, PageRequest.of(page, size)); |
|
| 102 |
public Page<OrganizationSimpleView> findByType(@PathVariable final String type, |
|
| 103 |
@PathVariable final int page, |
|
| 104 |
@PathVariable final int size, |
|
| 105 |
final Authentication authentication) {
|
|
| 106 |
return User.isSuperUser(authentication) |
|
| 107 |
? organizationSimpleViewRepository.findByType(type, PageRequest.of(page, size)) |
|
| 108 |
: organizationSimpleViewRepository.findByTypeForUser(type, authentication.getName(), PageRequest.of(page, size)); |
|
| 99 | 109 |
} |
| 100 | 110 |
|
| 101 | 111 |
@RequestMapping(value = "/browse/countries", method = RequestMethod.GET) |
| modules/dnet-orgs-database-application/trunk/src/main/resources/sql/samples.sql | ||
|---|---|---|
| 11 | 11 |
INSERT INTO user_countries VALUES ('paolo', 'IT');
|
| 12 | 12 |
INSERT INTO user_countries VALUES ('paolo', 'GR');
|
| 13 | 13 |
INSERT INTO user_countries VALUES ('paolo', 'GB');
|
| 14 |
INSERT INTO user_countries VALUES ('paolo', 'US');
|
|
| modules/dnet-orgs-database-application/trunk/src/main/resources/sql/schema.sql | ||
|---|---|---|
| 139 | 139 |
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,
|
| 140 | 140 |
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang)) FILTER (WHERE n.name IS NOT NULL), '[]') AS other_names,
|
| 141 | 141 |
COALESCE(jsonb_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL), '[]') AS acronyms, |
| 142 |
COALESCE(jsonb_agg(DISTINCT t.type::text) FILTER (WHERE t.type IS NOT NULL), '[]') AS types,
|
|
| 142 |
COALESCE(jsonb_agg(DISTINCT t.type) FILTER (WHERE t.type IS NOT NULL), '[]') AS types,
|
|
| 143 | 143 |
COALESCE(jsonb_agg(DISTINCT u.url) FILTER (WHERE u.url IS NOT NULL), '[]') AS urls |
| 144 | 144 |
FROM |
| 145 | 145 |
organizations org |
| ... | ... | |
| 165 | 165 |
org.name, |
| 166 | 166 |
org.city, |
| 167 | 167 |
org.country, |
| 168 |
array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms,
|
|
| 169 |
array_agg(DISTINCT t.type::text) FILTER (WHERE t.type IS NOT NULL) AS types
|
|
| 168 |
array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms, |
|
| 169 |
array_agg(DISTINCT t.type) FILTER (WHERE t.type IS NOT NULL) AS types
|
|
| 170 | 170 |
FROM |
| 171 | 171 |
organizations org |
| 172 |
LEFT OUTER JOIN acronyms a ON (org.id = a.id)
|
|
| 173 |
LEFT OUTER JOIN types t ON (org.id = t.id)
|
|
| 172 |
LEFT OUTER JOIN acronyms a ON (org.id = a.id) |
|
| 173 |
LEFT OUTER JOIN types t ON (org.id = t.id) |
|
| 174 | 174 |
GROUP BY |
| 175 | 175 |
org.id, |
| 176 | 176 |
org.name, |
Also available in: Unified diff