Решение HappyNomad было очень хорошим и помогло мне в конечном итоге прийти к этому немного другому решению.
<ComboBox x:Name="ComboBoxUploadProject"
Grid.Row="2"
Width="200"
Height="23"
Margin="64,0,0,0"
ItemsSource="{Binding projectList}"
SelectedValue ="{Binding projectSelect}"
DisplayMemberPath="projectName"
SelectedValuePath="projectId"
>
<ComboBox.Template>
<ControlTemplate TargetType="ComboBox">
<Grid>
<ComboBox x:Name="cb"
DataContext="{Binding RelativeSource={RelativeSource TemplatedParent}}"
ItemsSource="{Binding ItemsSource, RelativeSource={RelativeSource TemplatedParent}}"
SelectedValue ="{Binding SelectedValue, RelativeSource={RelativeSource TemplatedParent}}"
DisplayMemberPath="projectName"
SelectedValuePath="projectId"
/>
<TextBlock x:Name="tb" Text="Select Item..." Margin="3,3,0,0" IsHitTestVisible="False" Visibility="Hidden"/>
</Grid>
<ControlTemplate.Triggers>
<Trigger SourceName="cb" Property="SelectedItem" Value="{x:Null}">
<Setter TargetName="tb" Property="Visibility" Value="Visible"/>
</Trigger>
</ControlTemplate.Triggers>
</ControlTemplate>
</ComboBox.Template>
</ComboBox>
Попробуйте
SELECT w1.users_id AS user1,u.name as name, p.*, u.*, w2.users_id
AS user2, COUNT(w2.watched) AS num_movies,
GROUP_CONCAT(w2.watched ORDER BY w2.watched) AS movies
FROM watched w1
JOIN watched w2 ON w2.watched = w1.watched
AND w2.users_id != w1.users_id
JOIN users u ON u.id=w1.users_id
JOIN profile p ON p.users_id=w1.users_id
WHERE w1.users_id =1 GROUP BY user1, user2
Поместите соединение с profile
после соединения с watched
, которое получает идентификатор другого пользователя.
SELECT p.*, w2.watched,
COUNT(*) AS num_movies,
GROUP_CONCAT(w2.watched order by w2.watched) as movies
FROM watched AS w1
JOIN watched w2 ON w2.watched = w1.watched AND w2.users_id != w1.users_id
JOIN users AS u ON u.id = w2.users_id
JOIN profile AS p ON p.users_id = u.id
WHERE w1.users_id = $id
GROUP BY w2.users_id
Чтобы получить необработанные данные, которые вам нужны, вам просто нужно JOIN
таблицы users
и profile
к существующему запросу, один раз для каждого пользователя:
SELECT w1.users_id AS id1,
u1.name AS user1,
u1.email AS user1_email,
p1.about AS user1_about,
w2.users_id AS id2,
u2.name AS user2,
u2.email AS user2_email,
p2.about AS user2_about,
COUNT(w2.watched) AS num_movies,
GROUP_CONCAT(w2.watched ORDER BY w2.watched) AS movies
FROM watched w1
JOIN watched w2 ON w2.watched = w1.watched AND w2.users_id != w1.users_id
JOIN users u1 ON u1.id = w1.users_id
JOIN profile p1 ON p1.users_id = u1.id
JOIN users u2 ON u2.id = w2.users_id
JOIN profile p2 ON p2.users_id = u2.id
WHERE w1.users_id = 1
GROUP BY id1, user1, user1_email, user1_about, id2, user2, user2_email, user2_about
Вывод: [118 ]
id1 user1 user1_email user1_about id2 user2 user2_email user2_about num_movies movies
1 name1 email1@mail.com something about me 2 name2 email2@mail.com something about me 2 movie1,movie2
1 name1 email1@mail.com something about me 3 name3 email3@mail.com something about me 1 movie1
В PHP вы можете сделать что-то вроде:
$id = Чтобы получить необработанные данные, которые вам нужны, вам просто нужно JOIN
таблицы users
и profile
к существующему запросу, один раз для каждого пользователя:
[110] Вывод: [118 ] [111]
В PHP вы можете сделать что-то вроде:
[112]SESSION['id'];
$first = true;
$movies = mysqli_query($connect, "... the query above ...");
while ($row = $movies->fetch_assoc()) {
if ($first) {
echo "User {$row['user1']} ({$row['user1_email']}, {$row['user1_about']}):\n";
$first = false;
}
echo "matched with {$row['user2']} ({$row['user2_email']}) {$row['num_movies']} times on titles {$row['movies']}\n";
}