Całą idea tego wyszukiwania jest taka, żeby wyszukać nastroje w okręslonych data poziomach nastroju, ale ja bym chciAŁ OMÓWIĆ tylko te kryteria, które są potrzebne teraz do wytłumaczenia i do sporządzenia algorytmu.
Jest sobie panel wyszukiwania i w tym panelu jest dodatkowa opcja grupuj wg dnia i wszystko działa dobrze w momencia jak do nastroju jest przyporządkowana tylko jedna akcja lub nie ma żadnej, ale w monecie jak jest >= 2 akcje to dany rekord bierze jako dwa rekordy. Ja może dam kod źródłowy.
$startDay = $this->startDay;
$moodModel = new MoodModel;
$bool = $this->searchAction($request->get("action"));
$moodModel->createQuestionGroupDay($this->startDay,$bool,$request->get("ifAction"));
if (($request->get("ifAction")) == "on" ) {
$moodModel->groupMoodAction();
}
if (!empty($request->get("action")) and ($request->get("action") != "undefined") ) {
$moodModel->searchActionGroup($request->get("action"),(array)$request->get("actionFrom"),(array)$request->get("actionTo"));
}
$moodModel->setGroupDay(Auth::User()->start_day);
$this->count = $moodModel->questions->get()->count();
return $moodModel->questions->paginate(15);
Teraz kod funkcji modelu
public function createQuestionGroupDay(int $startDay,bool $ifAction,$actionOn) {
$this->questions = self::query();
$this->questions
->selectRaw("sum(TIMESTAMPDIFF (minute, moods.date_start , moods.date_end)) as longMood")
//->selectRaw("sum(moods.level_mood) as level_mood")
->selectRaw(" round(sum( ( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ) * moods.level_mood) / sum( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ),3 ) as level_mood ")
->selectRaw(" round(sum( ( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ) * moods.level_anxiety) / sum( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ),3 ) as level_anxiety ")
->selectRaw(" round(sum( ( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ) * moods.level_nervousness ) / sum( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ),3 ) as level_nervousness ")
->selectRaw(" round(sum( ( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ) * moods.level_stimulation) / sum( unix_timestamp(moods.date_end) - unix_timestamp(moods.date_start) ),3 ) as level_stimulation ")
->selectRaw("(count(moods.id ) ) as count")
->selectRaw("min(moods.date_start) minMood")
->selectRaw("max(moods.date_end) maxMood")
->selectRaw(DB::Raw("(DATE(IF(HOUR( moods.date_start) >= '" . $startDay . "', moods.date_start,Date_add(moods.date_start, INTERVAL - 1 DAY) )) ) as datStart " ))
->selectRaw(DB::Raw("(DATE(IF(HOUR( moods.date_end) >= '" . $startDay . "', moods.date_end,Date_add(moods.date_end, INTERVAL - 1 DAY) )) ) as datEnd" ));
// if ($ifAction == true or $actionOn == "on") {
// $this->questions->crossjoin("moods_actions","moods_actions.id_moods","moods.id");
// //->join("actions","actions.id","moods_actions.id_actions");
//
// }
}
public function groupMoodAction() {
$this->questions->whereExists(function ($query) {
$query
->select(DB::raw(1))
->from('moods_actions')
->whereColumn('moods_actions.id_moods', 'moods.id');
});
//$this->questions->groupBy("moods_actions.id_actions");
}
public function searchActionGroup(array $action,array $actionFrom,array $actionTo)
{
$this->questions->whereExists(function ($query) use ($action, $actionFrom, $actionTo) {
$query
->select(DB::raw(1))
->from('actions');
for ($i = 0; $i < count($action); $i++) {
if ($action[$i] == "NULL") {
continue;
}
if ($action[$i] != "" and (!empty($actionFrom) and (!empty($actionTo))) and ($actionFrom[$i] != "" and $actionTo[$i] != "")) {
$query->orwhereRaw("("
. "actions.name like '%" . $action[$i] . "%' and (" .
"(CASE "
. " WHEN moods_actions.percent_executing is NULL && moods_actions.minute_exe is NULL THEN (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. " WHEN moods_actions.minute_exe is NOT NULL THEN (moods_actions.minute_exe) "
. " WHEN moods_actions.percent_executing is NOT NULL THEN ( moods_actions.percent_executing / 100) * (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. " "
. " END) >='" . $actionFrom[$i] . "')"
. "and ("
. "(CASE "
. " WHEN moods_actions.percent_executing is NULL && moods_actions.minute_exe is NULL THEN (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. " WHEN moods_actions.minute_exe is NOT NULL THEN (moods_actions.minute_exe) "
. " WHEN moods_actions.percent_executing is NOT NULL THEN ( moods_actions.percent_executing / 100) * (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. " "
. " END) <='" . $actionTo[$i] . "')"
. ""
. ")"
);
} else if ($action[$i] != "" and ((!empty($actionTo))) and ($actionTo[$i] != "")) {
$query->orwhereRaw("("
. "actions.name like '%" . $action[$i] . "%' and (("
. " CASE "
. " WHEN moods_actions.percent_executing is NULL && moods_actions.minute_exe is NULL THEN (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. " WHEN moods_actions.minute_exe is NOT NULL THEN (moods_actions.minute_exe) "
. " WHEN moods_actions.percent_executing is NOT NULL THEN ( moods_actions.percent_executing / 100) * (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. ""
. " END)"
. ") <= '" . $actionTo[$i] . "')"
. " ");
} else if ($action[$i] != "" and (!empty($actionFrom)) and ($actionFrom[$i] != "")) {
$query->orwhereRaw("("
. "actions.name like '%" . $action[$i] . "%' and (("
. " CASE "
. " WHEN moods_actions.percent_executing is NULL && moods_actions.minute_exe is NULL THEN (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. " WHEN moods_actions.minute_exe is NOT NULL THEN (moods_actions.minute_exe) "
. " WHEN moods_actions.percent_executing is NOT NULL THEN ( moods_actions.percent_executing / 100) * (TIMESTAMPDIFF(minute,moods.date_start,moods.date_end) ) "
. " "
. " END)"
. ") >= '" . $actionFrom[$i] . "')"
. " ");
} else if ($action[$i] != "") {
$query->orwhereRaw("actions.name like '%" . $action[$i] . "%'");
}
}
});
}
public function setGroupDay(int $startDay) {
$this->questions->groupBy(DB::Raw("(DATE(IF(HOUR( moods.date_end) >= '" . $startDay . "', moods.date_end,Date_add(moods.date_end, INTERVAL - 1 DAY) ))) "));
}
I generalnie wcześniej nie miałem tej funkcji groupMoodAction (ta funkcja daje nam zapytanie w zapytaniu ) po jej dodaniu uwzględnia to ale teraz za to nie działa wyszukiwanie po nazwie akcji czyli funkcja searchActionGroup w momencie kiedy jest dana nazwa funkcji w którymś rekordzie to zwróci wszystkie rekordy które mają jakąś przypisaną akcje a nie tylko te które mają taką nazwę, ale co ciekawe jak wpisze nazwę akcji której nie ma żaden nasatrój to nic nie wyszuka.
Generalnie teraz tylko z tym mam problem czyli wyszukiwanie po nazwie.