| 37 | |
| 38 | #[cfg(feature = "postgres")] |
| 39 | pub async fn global_search( |
| 40 | user_id: &Uuid, |
| 41 | global_admin: bool, |
| 42 | search: &str, |
| 43 | limit: usize, |
| 44 | offset: usize, |
| 45 | ) -> Result<Vec<SearchResult>, Error> { |
| 46 | let (query, tags) = parse_search_query(search); |
| 47 | let query = format!("%{}%", query); |
| 48 | let tags = serde_json::to_value(tags).context("To serde_json value")?; |
| 49 | |
| 50 | let res: Vec<SearchResult> = diesel::sql_query( |
| 51 | r#" |
| 52 | -- device |
| 53 | select |
| 54 | 'device' as kind, |
| 55 | greatest(similarity(d.name, $1), similarity(encode(d.dev_eui, 'hex'), $1), similarity(encode(d.dev_addr, 'hex'), $1)) as score, |
| 56 | t.id as tenant_id, |
| 57 | t.name as tenant_name, |
| 58 | a.id as application_id, |
| 59 | a.name as application_name, |
| 60 | d.dev_eui as device_dev_eui, |
| 61 | d.name as device_name, |
| 62 | null as gateway_id, |
| 63 | null as gateway_name |
| 64 | from device d |
| 65 | inner join application a |
| 66 | on a.id = d.application_id |
| 67 | inner join tenant t |
| 68 | on t.id = a.tenant_id |
| 69 | left join tenant_user tu |
| 70 | on tu.tenant_id = t.id |
| 71 | left join tenant_user_application tua |
| 72 | on tua.user_id = tu.user_id and tua.application_id = a.id |
| 73 | left join "user" u |
| 74 | on u.id = tu.user_id |
| 75 | where |
| 76 | ($3 = true or (u.id = $4 and (tu.is_admin = true or tu.is_device_admin = true or tua.user_id = $4))) |
| 77 | and (d.name ilike $2 or encode(d.dev_eui, 'hex') ilike $2 or encode(d.dev_addr, 'hex') ilike $2 or ($7 != '{}'::jsonb and d.tags @> $7)) |
| 78 | -- gateway |
| 79 | union |
| 80 | select |
| 81 | 'gateway' as kind, |
| 82 | greatest(similarity(g.name, $1), similarity(encode(g.gateway_id, 'hex'), $1)) as score, |
| 83 | t.id as tenant_id, |
| 84 | t.name as tenant_name, |
| 85 | null as application_id, |
| 86 | null as application_name, |
| 87 | null as device_dev_eui, |
| 88 | null as device_name, |
| 89 | g.gateway_id as gateway_id, |
| 90 | g.name as gateway_name |
| 91 | from |
| 92 | gateway g |
| 93 | inner join tenant t |
| 94 | on t.id = g.tenant_id |
| 95 | left join tenant_user tu |
| 96 | on tu.tenant_id = t.id |