Django Haystack/Solr: Faceting on a model but show results only from a ForeignKey field
Date : March 29 2020, 07:55 AM
I hope this helps you . I solved this with the help of Daniel Lindsay(Haystack/pySolr author) on haystack mailing list. from haystack import indexes
class Medicine(indexes.SearchIndex):
field_1 = indexes.MultiValuedField(faceted=True)
# Other field definitions
def prepare_field_1(self, object):
values = list()
for app in object.applications.all():
values.append(app.field_on_which_to_facet)
return values
# define "prepare_fieldname" methods for other fields in similar fashion.
|
Finding the number of haystack results by model type?
Date : March 29 2020, 07:55 AM
will help you Have you tried just creating another facet for that? The only potentially difficult part would be getting the model name. Most likley, you would want to use verbose_name so it's more readable, which you can get from _meta.verbose_name. However, something like the following code probably wouldn't work: model = CharField(model_attr='_meta.verbose_name', faceted=True)
def get_model_name(self):
return self._meta.verbose_name
model = CharField(model_attr='get_model_name', faceted=True)
|
Count Results Based on Categorize Range Age (Query MySQL)
Date : March 29 2020, 07:55 AM
This might help you I have a table like this: , You need a derived table, in order to get the rows with a zero count: select r.range, count(t.age)
from (select '15-19' as range, 15 as lo, 19 as hi union all
select '20-44' as range, 20 as lo, 44 as hi union all
. . .
) r left join
t
on t.age >= r.lo and t.age <= r.hi
group by r.range
order by min(r.lo);
|
Categorize Data within table and count based upon results
Tag : sql , By : Jakub Filak
Date : March 29 2020, 07:55 AM
this one helps. As the bound conditions you have provided here seems pretty much static, we can create a derived table that uses the T-SQL table value constructor to specify multiple rows for the base table as: select
BT.Lives_Category ,
count(CT.clean_ft_ee_cnt) as Number_of_Clients
from @mvp_client_data_t CT
join
--base table here
(
select * from
(values ('1-9',1,9),
('10-49',10,49),
('50-199',50,199)
)
as Base_Table (Lives_Category,MinCnt,MaxCnt)
) BT
on CT.clean_ft_ee_cnt between BT.MinCnt and BT.MaxCnt
group by BT.Lives_Category
|
Combine two queries that both categorize the data based upon the results
Tag : sql , By : mobi phil
Date : March 29 2020, 07:55 AM
will help you I do not know enough about your data to know if you should use a inner, left, right, or full join. So you should modify the join to suit your situation, but this should get you on the right track. Basically, I put each your your select statement in a subquery and join on the common field, "Lives Category". SELECT COALESCE(x.[Lives Category], y.[Lives Category]) AS [Lives Category]
, x.[MVP Number of Clients]
, x.[MVP Sum of Lives]
, y.[Number of Clients]
, y.[Sum of Lives]
FROM (
SELECT t1.Lives_Category AS [Lives Category]
, COUNT(t3.clean_ft_ee_cnt) AS [MVP Number of Clients]
, SUM(t3.clean_ft_ee_cnt) AS [MVP Sum of Lives]
FROM mvpGA.mvp_client_data_t AS t3
JOIN (
SELECT *
FROM (
VALUES ('1-9 Lives', 1, 9)
, ('10-49 Lives', 10, 49)
, ('50-199 Lives', 50, 199)
, ('200-499 Lives', 200, 499)
, ('500-1,9999 Lives', 500, 1999)
, ('2,000+ Lives', 2000, 100000000)
, ('Total Lives', 0, 100000000)
, ('500+ Lives', 500, 1000000000)
, ('Unknown Lives', 0, 0)
) AS Base_Table (Lives_Category, MinCnt, MaxCnt)
) AS t1 ON t3.clean_ft_ee_cnt BETWEEN t1.MinCnt AND t1.MaxCnt
WHERE t3.mvp_rpt_id > 1399
AND t3.clean_do_not_use_ind IS NULL
AND t3.clean_client_indv_flag = 'Group'
GROUP BY t1.Lives_Category
) AS x
FULL JOIN (
SELECT DISTINCT
a1.Lives_Category AS [Lives Category]
, COUNT(t2.clean_ft_ee_cnt) AS [Number of Clients]
, SUM(t2.clean_ft_ee_cnt) AS [Sum of Lives]
FROM mvpGA.mvp_client_data_t AS t2
JOIN (
SELECT *
FROM (
VALUES ('1-9 Lives', 1, 9)
, ('10-49 Lives', 10, 49)
, ('50-199 Lives', 50, 199)
, ('200-499 Lives', 200, 499)
, ('500-1,9999 Lives', 500, 1999)
, ('2,000+ Lives', 2000, 100000000)
, ('Total Lives', 0, 100000000)
, ('500+ Lives', 500, 1000000000)
, ('Unknown Lives', 0, 0)
) AS Base_Table (Lives_Category, MinCnt, MaxCnt)
) AS a1 ON t2.clean_ft_ee_cnt BETWEEN a1.MinCnt AND a1.MaxCnt
WHERE t2.mvp_rpt_id = 1400
AND t2.clean_mvp_rpt_breakout = 'Fargo'
AND t2.clean_do_not_use_ind IS NULL
AND t2.clean_client_indv_flag = 'Group'
GROUP BY a1.Lives_Category
) AS y ON x.[Lives Category] = y.[Lives Category];
|
Boards Message :
Related QUESTIONS :