Can I use JOIN?

[calraiden]

Hi, I'm starting in the ionic and would like to use with sqlite, but don't found examples with JOIN.
Is it possible?

Example:

var query = "SELECT b.name, c.number, v.number, v.text, v.id, v.chapter_book_id, v.chapter_id, v.chapter_testament_id " +
" FROM verse AS v JOIN book AS b ON b.id = v.chapter_book_id " +
" JOIN chapter AS c ON c.id = v.chapter_id " +
" LIMIT 1";

$cordovaSQLite.execute(db, query, []).then(function(res) {
	if (res.rows.length > 0) {
		var n = res.rows.item(0);
		console.log(n);
	}
}, function(err){
	console.log(err.message);		
});

[calraiden]

Sorry, I was wrong.

I was trying to join, but it was closing the app. I thought it was because of the join, but the problem was encoding iso8859-1.

After several tests, I found a bug and switched encode to UTF8 in the database.

Now, join working very well.

		var query = "SELECT b.id, b.name, b.symbol, b.number, b.chapters, b.testament_id, b.keywords_id, k.color_background AS keycolor FROM book AS b LEFT JOIN keywords AS k ON k.id=b.keywords_id ORDER BY b.id ASC";
		$cordovaSQLite.execute(db, query).then(function(resultSet) {
			if (resultSet.rows.length > 0) {
	            for(var x = 0; x < resultSet.rows.length; x++) {
	            	if(resultSet.rows.item(x).testament_id == 1){
	            		$scope.bookListNewTestament.push(resultSet.rows.item(x));
	            	}else{
	            		$scope.bookListOldTestament.push(resultSet.rows.item(x));
	            	}
	            }				
			} else {
				console.log('No records found');
			}
		});

[brodycj]

Thanks @calraiden. My understanding is that a sqlite database normally has UTF-8 or UTF-16 encoding as described in:

• https://www.sqlite.org/fileformat.html#text_encoding
• https://www.sqlite.org/pragma.html#pragma_encoding

and a sqlite database would have UTF-8 encoding by default:

• http://stackoverflow.com/questions/4276215/make-sure-the-sqlite-on-android-uses-utf-8-as-a-charset/4276758#4276758
• http://www.sqlite.org/c3ref/open.html

I marked this as a documentation item.

[brodycj]

FYI I raised #652 to fix the Windows platform version to use UTF-8 encoding by default (Android/iOS/macOS platform versions already use UTF-8 encoding by default).